Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave MySQL server. What parameter I should pass to mysqldump? Currently what I use is listed below.

mysqldump --host=localhost --user=root --password=pa4word --single-transaction --lock-all-tables --master-data=1 mydb > result.sql

Is this command OK? I don't know whether this is suit to my environment(both MyISAM and InnoDB engine tables in one database).

Thanks,

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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 !!!

share|improve this answer
1  
Thank you very much RolandoMySQLDBA!!! You've answered a lot of my questions. You just like a kind and knowledgeable friend. I'm very much appreciate! –  Yousui Dec 5 '11 at 17:43

It depends on your requirement.mysqldump has also other options.You can specify them using mysqldump --help and choose from there what parameters you require.

If you didn't want to give extra efforts this one is ok.

I basically use

mysqldump -uroot -proot --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.