Two ways to add slave database without stopping MySQL Service

Currently, the MySQL database in the production environment is a master database and a slave database is added due to the increasing access volume. The premise is that it cannot affect the use of online services, that is, it cannot restart the MySQL service. To avoid other situations, you can choose to operate during low-traffic periods. There are two ways to increase the number of slave databases online. One is to use mysqldump for backup.

Currently, the MySQL database in the production environment is a master database and a slave database is added due to the increasing access volume. The premise is that it cannot affect the use of online services, that is, it cannot restart the MySQL service. To avoid other situations, you can choose to operate during low-traffic periods. There are two ways to increase the number of slave databases online. One is to use mysqldump for backup.

Currently, the MySQL database in the production environment is a master database and a slave database is added due to the increasing access volume. The premise is that it cannot affect the use of online services, that is, it cannot restart the MySQL service. To avoid other situations, you can choose to operate during low-traffic periods.

There are two ways to increase the number of slave databases online. One is to back up the master database through mysqldump and restore to the slave database. mysqldump is a logical backup. When the data volume is large, the backup speed will be slow, it takes a long time to lock the table. The other is to use the xtrabackup tool to back up the master database and restore it to the slave database. xtrabackup is a physical backup, which is fast and does not lock the table. Why not lock the table? Because it monitors the logs of the master database, if there is updated data, it will first be written to a file, and then return to the backup file to maintain data consistency.

Server Information:

Master Database: 192.168.18.212 (original)

Slave database 1: 192.168.18.213 (original)

Slave database 2: 192.168.18.214 (new)

Database Version: MySQL5.5

Storage engine: Innodb

Test Database Name: weibo

I. mysqldump Mode

MySQL Master/Slave is based on binlog, so binlog must be enabled after the database is installed. The advantage is that, on the one hand, you can use binlog to restore the database, and on the other hand, you can prepare for the primary slave.

The original master database configuration parameters are as follows:

# Vi my. cnfserver-id = 1 # id must be unique log-bin = mysql-bin # enable binlog auto-increment = 1 # In Ubuntu system MySQL5.5, the default value is 1auto-increment-offset = 1 slave-skip-errors = all # Skipping master-slave replication errors

1. Create a synchronization account for the master database

mysql> grant all on *.* to 'sync'@'192.168.18.%' identified by 'sync';

2. Configure MySQL from the slave Database

# Vi my. cnfserver-id = 3 # This setting 3log-bin = mysql-bin # enable binlog auto-increment = 1 # these two parameters have been set to 1auto-increment-offset = by default after MySQL5.5 in Ubuntu 1 slave-skip-errors = all # error in skipping master-slave Replication

3. Back up the master database

# mysqldump -uroot -p123 --routines --single_transaction --master-data=2 --databases weibo > weibo.sql

Parameter description:

-- Routines: Export stored procedures and functions

-- Single_transaction: sets the transaction isolation status at the beginning of the export, starts the transaction with a consistent snapshot, and unlock tables; while lock-tables locks a table and cannot write operations until the dump is completed.

-- Master-data: The default value is 1. The binlog point and pos value of the dump start (change master to) are written to the result. If the value is 2, the change master to is written to the result and commented out.

4. Copy the backup database to the slave Database

# scp weibo.sql root@192.168.18.214:/home/root

5. Create a table test_tb in the master database to simulate new data in the database. weibo. SQL does not exist.

mysql> create table test_tb(id int,name varchar(30));

6. Import the backup database from the database

# mysql -uroot -p123 -e 'create database weibo;'# mysql -uroot -p123 weibo < weibo.sql

7. View binlog and pos values in the backup file weibo. SQL

# Head-25 weibo. SQL -- CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = 107; # About 22 rows

8. Set synchronization from this log point in the slave database and start

mysql> change master to master_host='192.168.18.212',    -> master_user='sync',    -> master_password='sync',    -> master_log_file='mysql-bin.000001',    -> master_log_pos=107;mysql> start slave;

mysql> show slave status\G;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    90Current database: *** NONE ****************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.18.212                  Master_User: sync                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 358               Relay_Log_File: mysqld-relay-bin.000003                Relay_Log_Pos: 504        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes......

The IO and SQL threads are both YES, indicating that the Master/Slave configuration is successful.

9. View tables in the weibo database from the database

mysql> show tables;+---------------------------+| Tables_in_weibo           |+---------------------------+| test_tb                   |

We found that the test_tb table we just created has been synchronized!

Ii. xtrabackup method (recommended)

Perform an experiment based on the above configuration and delete the slave Database Configuration first:

Mysql> stop slave; # stop synchronizing mysql> reset slave; # Clear the slave connection information mysql> show slave status \ G; # Check the slave status, the IO and SQL threads are both NOmysql> drop database weibo; # deleting the weibo database

Now, the database is the same as the newly installed one!

1. The master database uses xtrabackup for backup.

# innobackupex --user=root --password=123 ./

Generate a backup directory named after time: 2015-07-051116-49-43

# ll 2015-07-01_16-49-43/total 18480drwxr-xr-x 5 root root     4096 Jul  1 16:49 ./drwx------ 4 root root     4096 Jul  1 16:49 ../-rw-r--r-- 1 root root      188 Jul  1 16:49 backup-my.cnf-rw-r----- 1 root root 18874368 Jul  1 16:49 ibdata1drwxr-xr-x 2 root root     4096 Jul  1 16:49 mysql/drwxr-xr-x 2 root root     4096 Jul  1 16:49 performance_schema/drwxr-xr-x 2 root root    12288 Jul  1 16:49 weibo/-rw-r--r-- 1 root root       21 Jul  1 16:49 xtrabackup_binlog_info-rw-r----- 1 root root       89 Jul  1 16:49 xtrabackup_checkpoints-rw-r--r-- 1 root root      563 Jul  1 16:49 xtrabackup_info-rw-r----- 1 root root     2560 Jul  1 16:49 xtrabackup_logfile

2. Copy the Backup Directory to the slave database.

# scp -r 2015-07-01_16-49-43 root@192.168.18.214:/home/root

3. Stop MySQL from the database, delete the datadir directory, and rename the Backup Directory to the datadir directory.

# Sudo rm-rf/var/lib/mysql/# sudo mv 2015-07-051116-49-43 // var/lib/mysql # sudo chown mysql. mysql-R/var/lib/mysql # sudo/etc/init. d/mysql start # ps-ef | grep mysql # Check that mysql 8832 is started properly. 1 0? 00:00:00/usr/sbin/mysqld

4.Create a table test_tb2 in the master database to simulate new data in the database.

mysql> create table test_tb2(id int,name varchar(30));

5. Obtain the binlog and pos locations from the xtrabackup_info file in the backup directory.

# Cat/var/lib/mysql/xtrabackup_info uuid = 201af9db-1fce-11e5-96b0-525400e4239dname = tool_name = innobackupextool_command = -- user = root -- password = .... /tool_version = 1.5.1-xtrabackupibbackup_version = xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id:) server_version = 5.5.43-0ubuntu0. 12.04.1-logstart_time = 2015-07-01 16: 49: 43end_time = 2015-07-01 16: 49: 46lock_time = 1binlog_pos = filename 'mysql-bin.000001 ', position 429 # innodb_from_lsn = 0innodb_to_lsn = 1598188 partial = Nincremental = Nformat = filecompact = Ncompressed = N

6. Set synchronization from this log point in the slave database and start

mysql> change master to master_host='192.168.18.212',    -> master_user='sync',    -> master_password='sync',    -> master_log_file='mysql-bin.000001',    -> master_log_pos=429;mysql> start slave;

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.18.212                  Master_User: sync                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 539               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 363        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes......

The IO and SQL threads are both YES, indicating that the Master/Slave configuration is successful.

9. View tables in the weibo database from the database

mysql> show tables;+---------------------------+| Tables_in_weibo           |+---------------------------+| test_tb                   || test_tb2                  |

We found that the test_tb2 table we just created has been synchronized.

More Xtrabackup use see blog: http://lizhenliang.blog.51cto.com/7876557/1612800