If you use mysqldump, you can do the following
- Assure that no DDL occurs (no
ALTER TABLE
, no DROP TABLE
) during the mysqldump
- Use --single-transaction to make sure all tables have the same point-in-time
You can arrange a mysqldump to push the data directly into the slave without saving a dump file. Just pipe the mysqldump straight into the slave. You must preset the replication pieces beforehand.
Create a bash script called LoadSlave.sh
with these lines
MYSQL_MAS=10.20.30.40
MYSQL_SLV=10.20.30.50
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_CONN_MAS="-h${MYSQL_MAS} -u${MYSQL_CONN}"
MYSQL_CONN_SLV="-h${MYSQL_SLV} -u${MYSQL_CONN}"
REPL_USER=replicator
REPL_PASS=whateverthereplicatorpasswordis
DATABASE_TO_DUMP=mydb
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} -B ${DATABASE_TO_DUMP}"
SQL="STOP SLAVE;"
SQL="${SQL} CHANGE MASTER TO master_host='${MYSQL_MAS},master_port=3306,"
SQL="${SQL} master_user='${REPL_USER},master_password='${REPL_PASS}',"
SQL="${SQL} master_log_file='blank',master_log_pos=4;"
mysql ${MYSQL_CONN_SLV} -ANe"${SQL}"
mysqldump ${MYSQL_CONN_MAS} ${MYSQLDUMP_OPTIONS} | mysql ${MYSQL_CONN_SLV}
mysql ${MYSQL_CONN_SLV} -ANe"START SLAVE;"
Then, run the following
chmod +x LoadSlave.sh
nohup ./LoadSlave.sh &
This will set up replication mechanism, transmit the mysqldump through the network straight to the slave, drop the database on the slave, create the database on the slave, load the data into the slave, and start replication.
Please test this out on Dev/Staging Servers
As highlighted in Károly Nagy's answer, you could use percona-xtrabackup
. Just keep in mind the major difference between mysqldump
and percona-xtrabackup
- If you start
mysqldump --single-transaction
, at midnight and the backup takes 30 min, the backup's point-in-time is the start time of the mysqldump, 12:00 AM.
- If you start
percona-xtrabackup
, at midnight and the backup takes 30 min, the backup's point-in-time is the completion time of the backup, 12:30 AM.