Jephe Wu - http://linuxtechres.blogspot.com
Environment: CentOS 6, Mysql 5.1 64bit
Objective: create one or more mysql slaves from master server.
Steps:
1. setup master server
Some important configuration in /etc/my.cnf as follows:
server_id=1
log_bin = /srv/mysql/log/mysql-bin.log
log_bin_index = /srv/mysql/log/mysql-bin.log.index
relay_log = /srv/mysql/log/mysql-relay-bin
relay_log_index = /srv/mysql/log/mysql-relay-bin.index
log_slave_updates = 1
expire_logs_days = 2
max_binlog_size = 100M
binlog-format=MIXED
log-queries-not-using-indexes
Method 1: mysqldump
Refer to http://mysql-mmm.org/mmm2:guide for how to setup master-slave replication.
- since master db is running and it's necessary to flash all tables and lock database to prevent any changes before checking the binlog position.
mysql> flush tables with read lock;
mysql>show master status\G
Note: DON'T CLOSE this mysql-shell. If you close it, the database lock will be removed. Open a second console and type:
- export data
$ mysqldump -uroot -ppassword --host=master_host -all-databases [
--master-data=1] > dumpfile.sql
The --master-data=1 will record the 'change master to ' statement and write the master database information and position into dump file, so you don't have to use 'change master to' again after import.
- unlock tables from first point
mysql> UNLOCK TABLES;
- restore dumped file to slave
Method 2: mylvmbackup - http://www.lenzg.net/mylvmbackup/
According to ,mylvmbackup website,
To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.
This way, you can use LVM snapshot to do backup, master server can assume operation as quickly as possible. we can just transfer those snapshoted data and log files to slave site, modify server-id configuration in /etc/my.cnf for slave then start mysqld.
Method 3: shutdown master, copy data
Then change /etc/my.cnf to use different server-id, then start up slave db.
then use 'change master to =' to specify the next master bin log without position, the default position is 4 , mysql knows.
Everytime, you stop then start mysql primary server, it will start from a new binary log file and slave should start to replicate from this new binary log and default position 4.
then use 'change master to =' to specify the next master bin log without position, the default position is 4 , mysql knows.
Everytime, you stop then start mysql primary server, it will start from a new binary log file and slave should start to replicate from this new binary log and default position 4.
Method 4: create slave from another slave
If you have the current slave running, you might want to create another slave, you can make clone from the current slave database.
Method 4-1: flush tables and read lock
- firstly, stop slave first, mysql -uroot -ppassword,
mysql> flush tables with read lock;
mysql> show slave status;
keep this one, do not close it.
- backup data by using mysqldump
- imported the dumped data into the second slave.
- flush privileges for the second slave after import.
- exit the flush table with read lock session
Method 4-2: mylvmbackup, make LVM snapshot for mysql partition.
Just leave server-id different in my.cnf, everything else should be same.
Method 4-3: stop slave process, shutdown mysqld on first slave, use mylvmbackup to make snapshot then bring server up again(optional), copy snapshot data/log to the second slave;
Method 4-4: stop slave db completely, copy data folder only to the third slave , changed /etc/my.cnf server-id, then startup third slave , that's it
Method 4-4: stop slave db completely, copy data folder only to the third slave , changed /etc/my.cnf server-id, then startup third slave , that's it
3. master-master
After you have done first slave and master, you might want to make master as slave for the current slave. You can do so.
run 'show master status\G' on current slave to record down position, then use 'change master to ' on master server to make sync with
Posts
