Bash script for adding a mysql instance

Because I wanted to use one server to replicate multiple mysql masters I had to create various mysql instances from the same mysql install, which got tedious, so I made a script. Just enter the idbase (that with the port appended will become your sever id for replication), the port and the password you want for root.

It will first create the data directory and config file. After that it starts the server, sets the root password and then it adds the server info to the phpmyadmin file so you can select it when logging in.

idbase=132 #Because the local address of this example server is, easy to identify
port=3307 #Up by 1 for each instance

#Create the data directory and log directory (if you uncomment it)
mkdir /var/lib/mysql$port
chown -R mysql.mysql /var/lib/mysql$port/
#Uncomment these if you want to enable logging
#mkdir /var/log/mysql$port
#chown -R mysql.mysql /var/log/mysql$port
cp /etc/mysql/my.cnf /etc/mysql/my$port.cnf

#Configure the server. Tested on mysql version 5.5.28
sed -i "s/#server-id.*=.*/server-id = $idbase$port/" $file
sed -i "s/server-id.*=.*/server-id = $idbase$port/" $file
sed -i "s#^\(port\s*=\s*\).*\$#\1$port#" $file
sed -i "s#^\(socket\s*=\s*\).*\$#\1/var/run/mysqld/mysqld$port.sock#" $file
sed -i "s#^\(pid-file\s*=\s*\).*\$#\1/var/run/mysqld/mysqld$" $file
sed -i "s#^\(datadir\s*=\s*\).*\$#\1/var/lib/mysql$port#" $file
sed -i '/^bind-address/s/bind-address.*=.*/#bind-address =' $file

#Install the db, start it, then set root password
mysql_install_db --user=mysql --datadir=/var/lib/mysql$port/
nohup mysqld_safe --defaults-file=/etc/mysql/my$port.cnf &
sleep 3 #there's no telling if this waits long enough for the instance to start, so make sure the next command executed succesfully
mysqladmin -S /var/run/mysqld/mysqld$port.sock -u root password $rootpass

#Add the new server to phpmyadmin
echo "\$i++;
\$cfg['Servers'][\$i]['verbose'] = 'localhost $port';
\$cfg['Servers'][\$i]['host'] = '';
\$cfg['Servers'][\$i]['port'] = '$port';
\$cfg['Servers'][\$i]['socket'] = '';
\$cfg['Servers'][\$i]['connect_type'] = 'tcp';
\$cfg['Servers'][\$i]['extension'] = 'mysqli';
\$cfg['Servers'][\$i]['auth_type'] = 'cookie';" >> /etc/phpmyadmin/

Don’t forget to add the startup command for each instance to /etc/rc.local if you want it to start at boot:

nohup mysqld_safe --defaults-file=/etc/mysql/my3307.cnf &

To reboot:

mysqladmin -S /var/run/mysqld/mysqld3307.sock --password=xx shutdown
nohup mysqld_safe --defaults-file=/etc/mysql/my3307.cnf >> /dev/null 2>&1 &

Options altered by script:

#edit /etc/mysql/my$port.cnf
port = $port
socket = /var/run/mysqld/mysqld$port.sock

socket = /var/run/mysqld/mysqld$port.sock

pid-file = /var/run/mysqld/mysqld$
socket = /var/run/mysqld/mysqld$port.sock
port = $port
datadir = /var/lib/mysql$port
#bind-address = #comment it out
server-id = $idbase$port

I also made this script to quickly configure the slave. This assumes you used the first script because it uses the same file structure (/etc/mysql/my$slave_port.cnf etc.).

#User config

#Vars for ease of use
dbwilddo_done="replicate-wild-do-table = $dbwilddo"

#Read line 85 to see if config file is correctly positioned
readline=$(sed -n '85p' $conf_file)
#Read line 86 to see if config file is already altered
readline2=$(sed -n '86p' $conf_file)

if  [[ $readline == server-id* ]] ;
	if  [[ $readline2 == "$dbwilddo_done" ]] ;
		echo lines already added, skipping
		echo Updating mysql config file
		#Write the name of the table we want to replicate
		sed -i "86i$dbwilddo_done" $conf_file
		#--DISABLED--Write connection info--DISABLED--
		#write_one="master-host = $master_host"
		#sed -i "87i$write_one" $conf_file
		#write_one="master-port = $master_port"
		#sed -i "88i$write_one" $conf_file
		#write_one="master-user = $master_user"
		#sed -i "89i$write_one" $conf_file
		#write_one="master-password = $master_password"
		#sed -i "90i$write_one" $conf_file
	echo config file line 85 did not return the expected value

echo "Restarting mysql"
mysqladmin -S /var/run/mysqld/mysqld$slave_port.sock --password=$slave_pass shutdown
nohup mysqld_safe --defaults-file=/etc/mysql/my$slave_port.cnf >> /dev/null 2>&1 &
sleep 5 #Hopefully long enough
#mysqld_safe --defaults-file=/etc/mysql/my$slave_port.cnf restart #Doesn't work, still looking for a simpler method

#Create database
echo "Creating database if it doesn't already exist"
mysql --host= --port=$slave_port --password=$slave_pass -e "CREATE DATABASE IF NOT EXISTS $database"

#Set master
echo "Stopping slave"
mysql --host= --port=$slave_port --password=$slave_pass -e "STOP SLAVE;"
echo "Setting master"
mysql --host= --port=$slave_port --password=$slave_pass -e "CHANGE MASTER TO MASTER_HOST='$master_host',MASTER_USER='$master_user',MASTER_PASSWORD='$master_password',MASTER_PORT=$master_port;"

#Do manually:
#Import database(faster than just syncing, if db is big it'll save alot of time)
	#Master: mysqldump --password=xx dbname | gzip > /var/file.sql.gz
	#Transfer it
	#Slave: gunzip file.sql.gz
	#Slave: mysql --host= -P 3307 --password=xx dbname < /var/file.sql.gz
#Enable replication (START SLAVE;) + fix any errors (usually duplicate keys since sync/import).
#Sync with database (install pt-table-sync below) to make sure we missed nothing since the import
	#pt-table-sync --execute --databases dbname h=master,u=muser,p=mpass h=,u=root,p=xx
#Alternatively lock the write table prior to dump+import and then release lock when done. I don't do this because above method avoids downtime.
#Install pt-table-sync if you need it. This code is for Debian distros only!
	#cd /var
	#dpkg -i percona-toolkit_2.1.7_all.deb
	#apt-get -f install
	#dpkg -i percona-toolkit_2.1.7_all.deb

Finding missing IDs

Stuff happens during initial replication, and you could end up missing a couple of IDs. For some reason pt-table-sync doesn't always pick them up for me so I made a linux shell command that detects the gaps, but you'll have to export all the IDs to a text file first, in reverse order:

nohup mysql --password=xx -e 'select id from tablename order by id desc' databasename > /home/ids.txt &

Then parse the file with the shell command. The first line and last two lines are just to keep track of how long it took. 1.5million IDs(ish) took me 57sec & that's on a slow server. Set the max id in i and have at it.

T="$(date +%s)"; \
i=1574115; \
while read line; do \
	if  [[ "$line" != "$i" ]] ; then \
		if [[ $i -lt 1 ]] ; then break; fi; \
		if  [[ $line -gt 1 ]] ; then \
			missingsequenceend=$(( $line + 1 )); \
			minusstr="-"; \
			missingsequence="$missingsequenceend$minusstr$i"; \
			expectnext=$(( $line - 1 )); \
			i=$expectnext; \
			echo -e "$missingsequence"; \
		fi; \
	else \
		i=$(( $i - 1 )); \
	fi; \
done \
< ids.txt; \
T="$(($(date +%s)-T))"; \
echo "Time in seconds: ${T}"

Leave a Reply

Your email address will not be published. Required fields are marked *