If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:
mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql
Since --opt is enabled by default, the following options are already enabled
--add-drop-table
--add-locks
--create-options
--quick
--lock-tables
--set-charset
--disable-keys
If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.
MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
sleep 3
SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql
mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"
The reason this is better to do is that --single-transaction
does not protect MyISAM from changing during the dump.
Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:
Step 01) Add this to /etc/my.cnf on the master
[mysqld]
log-bin=mysql-bin
Step 02) service mysql restart
This will enable binary logging on the master.
On the new slave, you can run the following command:
CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
Make sure the rep_username exists in the master. If it does not, run this command on the master:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';
Then do the mysqldump to /root/mydata.sql.
Move the /root/mydata.sql from the master to the slave.
Next, execute in the mysql client this command on the slave:
source /root/mydata.sql
This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.
Finally, run START SLAVE;
on the slave and mysql replication should get going.
Run SHOW SLAVE STATUS\G
to check replication status.
Give it a Try !!!
Best Answer
If you only want a dump of the schema there is really no point in using the --single-transaction option. This option is more useful towards obtaining consistent data from transactional tables. In fact, while using this option, the dump is still vulnerable to being corrupt if any of the following statements are executed: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.
You can read more on the description for this option on the MySQL Documentation for 5.0.