Two ways to add slave databases without stopping the MySQL service

Now the MySQL database in the production environment has one master and one slave. Due to the increasing business volume, another slave database is added. The premise is that it cannot affect online business use, which means that the MySQL service cannot be restarted. In order to avoid other situations, choose to operate during the low peak period of website traffic.

Generally there are two ways to add a slave database online. One is to back up the main database through mysqldump and restore to the slave database. Mysqldump is a logical backup. When the amount of data is large, the backup speed will be very slow and the table lock time will be very long. . The other is to back up the main database through the xtrabackup tool and restore it to the slave database. xtrabackup is a physical backup, which has fast backup speed and does not lock tables. Why not lock the table? Because it monitors the main database log, if there is updated data, it will be written to a file first, and then returned to the backup file to maintain data consistency.

Server information:

Main 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 library name: weibo

1. mysqldump method

MySQL master-slave is based on binlog log, so binlog must be turned on after installing the database. The advantage of this is that on the one hand, you can use binlog to restore the database, and on the other hand, you can prepare for the master and slave.

The original main library configuration parameters are as follows:

# vi my.cnf

server-id = 1           #The id must be unique

log-bin = mysql-bin                       #Enable binlog log

auto-increment-increment= 1 #In Ubuntu system, MySQL 5.5 and later has defaulted to 1

auto-increment-offset = 1

slave-skip-errors =all #Skip errors that occur in master-slave replication

1. Create a synchronization account for the master library

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

2. Configure MySQL from the library

# vi my.cnf

server-id = 3         # This setting 3

log-bin = mysql-bin #Turn on binlog log

auto-increment-increment= 1 #These two parameters have defaulted to 1

auto-increment-offset in the Ubuntu system after MySQL5.5 = 1

slave-skip-errors =all #Skip errors in master-slave replication

3. Back up the master library

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

Parameter description:

--routines: Export stored procedures and functions

--single_transaction: Set the transaction isolation status at the beginning of the export, and use a consistent snapshot to start the transaction, and then unlock tables; while lock-tables locks a table and prevents write operations until the dump is completed.

--master-data: The default is equal to 1, and the dump starting (change master to) binlog point and pos value are written into the result. When equal to 2, the changemaster to is written into the result and commented.

4. Copy the backup library to the slave library

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

5. Create the test_tb table in the main library to simulate new data in the database, weibo.sql is No

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

6. Import the backup library from the library

# mysql -uroot -p123 -e'create database weibo;'

# mysql -uroot -p123weibo

7. Check the binlog and pos values ​​in the backup file weibo.sql

# head -25 weibo.sql

-- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 ; #About 22 lines

8. Synchronize the slave library settings from this log point and start

mysql> change masterto 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 slavestatusG;

ERROR 2006 (HY000 ): MySQLserver has gone away

No connection. Trying toreconnect...

Connection id: 90

Current database: ***NONE ***

******************************1. row * ****************************

SLAVE_IO_STATE: WAITING FORMASTER to Send Event

Master_host: 192.168.18.212

Master_user: Sync

Master_port ) Relay_Log_File:mysqld-relay-bin.000003

Relay_Log_Pos: 504

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yesl slave_sql_running: yes

......

9. Seeing the tables in the weibo library

can see that the IO and SQL threads are yes, indicating that the main configuration is successful.

mysql> show tables;

+--------------------------+

| Tables_in_weibo     |

+---- -----------------------+

| test_tb |

I found that the test_tb table that was simulated just now has been synchronized!

2. xtrabackup method (recommended)

Do experiments based on the above configuration, first delete the slave configuration:

mysql> stopslave;

mysql> show slavestatusG; #Check the slave status again, you can see that both the IO and SQL threads are NO

mysql> drop databaseweibo; #Delete the weibo library

At this time, the slave library is now the same as the newly installed one, move on!

1. Use xtrabackup to back up the main library

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

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

# ll 2015-07-01_16-49-43/

total 18480

drwxr-xr-x 5 rootroot 4096 Jul 1 16:49 ./

drwx------ 4 rootroot 4096 Jul 1 16:49 .. /

-rw-r--r-- 1 rootroot 188 Jul 1 16:49 backup-my.cnf

-rw-r----- 1 root root18874368 Jul 1 16:49 ibdata1

drwxr-xr -x 2 rootroot 4096 Jul 1 16:49 mysql/

drwxr-xr-x 2 rootroot 4096 Jul 1 16:49 performance_schema/

drwxr-xr-x 2 rootroot 12288 Jul 1 16:49 weibo/

-rw -r--r-- 1 rootroot 21 Jul 1 16:49 xtrabackup_binlog_info

-rw-r----- 1 rootroot 89 Jul 1 16:49 Jul 1 16:49 xtrabackup_info

-rw-r----- 1 rootroot 2560 Jul 1 16:49 xtrabackup_logfile

2. Copy the backup directory to the slave library

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

3. Stop the MySQL service from the database, delete the datadir directory, and rename the backup directory to the datadir directory

# sudo rm -rf/var/lib/mysql /

# sudo mv2015-07-01_16-49-43/ /var/lib/mysql

# sudo chown mysql.mysql -R /var/lib/mysql

# sudo /etc/init.d/mysqlstart

# ps -ef |grep mysql #Check that it has started normally

mysql 8832 1 0 16:55 ? 00:00:00 /usr/sbin/mysqld

4. Create the test_tb2 table in the main database and simulate new data in the database

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

5. From the backup directory, Xtrabackup_info file obtained Binlog and POS positions = =

tool_name = innobackupex

tool_command =--user=root --password=... ./

tool_version =1.5.1-xtrabackup

ibbackup_version =xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revisionid: )

server_version =5.5.43-0ubuntu0.12.04.1-log

start_time = 2015-07-0116:49:43

end_time = 2015-07-0116:49:46

lock_time = 1

binlog_pos = filename'mysql-bin.000001', position 429 #This position

innodb_from_lsn = 0

innodb_to_lsn = 1598188

partial = N

incremental = N

form at = file

compact = N

compressed = N

6. Synchronize the slave library settings from this log point and start

mysql> change masterto 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 slavestatusG;

****** *********************1. row *************************** *V SLAVE_IO_STATE: WAITING FORMASTER to Send Event

Master_host: 192.168.18.212

Master_user: Sync

Master_Port: 3306

bin.000001a Read_Master_LOG_POS: 539

Relay_log_file: mysql-bin.000002

                                                                                                                                      Slave_SQL_Running: Yes

......

7. View the weibo library from the library In the table

, you can see that both the IO and SQL threads are YES, indicating that the master-slave configuration is successful.

mysql> show tables;

+--------------------------+

| Tables_in_weibo     |

+---- -----------------------+

| test_tb |

| test_tb2 |

I found that the test_tb2 table created by the simulation just now has been synchronized.

Get Brothers IT Education’s original Linux operation and maintenance engineer video/detailed Linux tutorial for free. For details, please contact the official website customer service: http://www.lampbrother.net/linux/

Learn PHP, Linux, HTML5, UI, Android and other videos Tutorial (courseware + notes + video)! Contact Q2430675018

Participate in the event to receive the original video tutorial CD collection of Brothers: http://www.lampbrother.net/newcd.html