0

I wrote myself a small script that produces the following shell command.

echo "CHANGE MASTER TO MASTER_USER='slave_user';" | mysql -uroot -pXXX --host=SLAVE &&
echo "CHANGE MASTER TO MASTER_PASSWORD='slave_password';" | mysql -uroot -pXXX --host=SLAVE &&
echo "STOP SLAVE;" | mysql -uroot -pXXX --host=SLAVE &&
mysqldump -uroot -pXXX --host=MASTER --add-drop-database --master-data=1 --databases database1 database2 | mysql -uroot -pXXX --host=SLAVE &&
echo "START SLAVE;" | mysql -C -uroot -pXXX --host=SLAVE

It should be possible to be run either on the slave or the master. However I am aware that the advantage of compression using the -C switch is only given when run on the master.

My master server is configured with the following settings (only shown what differs fromthe default. I use mysql server version 5.1.41.

[mysqld]
bind-address           = 0.0.0.0   
max_connections        = 600 
server-id               = 1
log_bin                 = /media/mysql-ebs/log_bin/mysql-bin.log
binlog_do_db            = database1
binlog_do_db            = database2
datadir = /media/mysql-ebs/datadir

My slave(s) are configured as follows:

server-id               = UNIQUE-ID
replicate-do-db         = database1
replicate-do-db         = database2

The server runs MyIsam as well as InnoDB Tables.

My Questions are:

  • Did I miss anything?
  • Will data consistency be ensured even if clients try read/write during the backup?
  • Are there any problesm/considerations/suggestions for this command?
The Shurrican
  • 2,240
  • 7
  • 39
  • 60

2 Answers2

1

It just dawned on me.

This your original shell script

echo "CHANGE MASTER TO MASTER_USER='slave_user';" | mysql -uroot -pXXX --host=SLAVE &&
echo "CHANGE MASTER TO MASTER_PASSWORD='slave_password';" | mysql -uroot -pXXX --host=SLAVE &&
echo "STOP SLAVE;" | mysql -uroot -pXXX --host=SLAVE &&
mysqldump -uroot -pXXX --host=MASTER --add-drop-database --master-data=1 --databases database1 database2 | mysql -uroot -pXXX --host=SLAVE &&
echo "START SLAVE;" | mysql -C -uroot -pXXX --host=SLAVE

The first command should be "STOP SLAVE;" because you cannot perform any CHANGE MASTER TO commands with a running slave. Therefore, your code should read:

echo "STOP SLAVE;" | mysql -uroot -pXXX --host=SLAVE &&
echo "CHANGE MASTER TO MASTER_USER='slave_user';" | mysql -uroot -pXXX --host=SLAVE &&
echo "CHANGE MASTER TO MASTER_PASSWORD='slave_password';" | mysql -uroot -pXXX --host=SLAVE &&
mysqldump -uroot -pXXX --host=MASTER --add-drop-database --master-data=1 --databases database1 database2 | mysql -uroot -pXXX --host=SLAVE &&
echo "START SLAVE;" | mysql -C -uroot -pXXX --host=SLAVE

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
0

So basically: * You change Replication User / Password on the SLAVE. * You stop replication on the slave. * You dump data from master to slave * You start slave Am I right ?

I this is the case I don't see the point of changing password. I guess you will first have to drop database on slave before dumping. You will need the log position on the master at the time you dump data otherwise, your slave won't know from where to start replicating. Instead of passing password on the command line, better use .my.cnf file

Jyce
  • 41
  • 1
  • the drop database command and the log positino should be included in the output of mysqldump by using the commands --add-drop-database and --master-data=1. i know the baseic procedure, i is just not always as simple and i wanted to have everything compact in one nice command. most of the time the password will not change, but it needs to be included in case it is not set up yet. the command is generated and executed dynamically with the appropriate usernames/passwords and hostnames. this is just an example. – The Shurrican Mar 16 '11 at 12:11