Two ways to add slave databases without stopping the MySQL service
- 怪我咯Original
- 2017-04-06 18:09:291474browse
The current production environmentMySQL database is a master and a slave. As the business volume continues to increase, a 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 fast. If it is slow, it will take a long time to lock the table. 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 will monitor 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 library 1: 192.168.18.213 (original)
Slave library 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 database configuration parameters are as follows:
# vi my.cnf server-id = 1 #id要唯一 log-bin = mysql-bin #开启binlog日志 auto-increment-increment = 1 #在Ubuntu系统中MySQL5.5以后已经默认是1 auto-increment-offset = 1 slave-skip-errors = all #跳过主从复制出现的错误
1. Create a synchronization account for the main database
mysql> grant all on *.* to 'sync'@'192.168.18.%' identified by 'sync';
2. Configure MySQL from the slave database
# vi my.cnf server-id = 3 #这个设置3 log-bin = mysql-bin #开启binlog日志 auto-increment-increment = 1 #这两个参数在Ubuntu系统中MySQL5.5以后都已经默认是1 auto-increment-offset = 1 slave-skip-errors = all #跳过主从复制出现的错误
3. Backup the main 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 when the export starts, and use the consistency snapshot to start the transaction, and then unlock tables; while lock -tables locks a table and cannot 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 to the result. When equal to 2, the change master to is written to the result. and annotate.
4. Copy the backup library to the slave library
# scp weibo.sql [email protected]:/home/root
5. Create the test_tb table in the main library and simulate the new database There is no data in weibo.sql
mysql> create table test_tb(id int,name varchar(30));
6. Import the backup library from the library
# mysql -uroot -p123 -e 'create database weibo;' # mysql -uroot -p123 weibo < weibo.sql
7. Check in the backup file weibo.sql binlog and pos values
# head -25 weibo.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; #大概22行
8. Set the slave library to synchronize from this log point 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 away
No connection. Trying to reconnect...
Connection id: 90
Current 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
......You can see that the IO and SQL threads are both YES , indicating that the master-slave configuration is successful.
9. View the tables in the weibo library from the library
mysql> show tables; +---------------------------+ | Tables_in_weibo | +---------------------------+ | test_tb |
发现刚才模拟创建的test_tb表已经同步过来！
二、xtrabackup方式（推荐）
在上面配置基础上做实验，先删除掉从库配置：
mysql> stop slave; #停止同步 mysql> reset slave; #清除从连接信息 mysql> show slave status\G; #再查看从状态，可以看到IO和SQL线程都为NO mysql> drop database weibo; #删除weibo库
此时，从库现在和新装的一样，继续前进！
1. 主库使用xtrabackup备份
# innobackupex --user=root --password=123 ./
生成一个以时间为命名的备份目录：2015-07-01_16-49-43
# ll 2015-07-01_16-49-43/ total 18480 drwxr-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 ibdata1 drwxr-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. 把备份目录拷贝到从库上
# scp -r 2015-07-01_16-49-43 [email protected]:/home/root
3. 从库上把MySQL服务停掉，删除datadir目录，将备份目录重命名为datadir目录
# sudo rm -rf /var/lib/mysql/ # sudo mv 2015-07-01_16-49-43/ /var/lib/mysql # sudo chown mysql.mysql -R /var/lib/mysql # sudo /etc/init.d/mysql start # ps -ef |grep mysql #查看已经正常启动 mysql 8832 1 0 16:55 ? 00:00:00 /usr/sbin/mysqld
4. 在主库创建test_tb2表，模拟数据库新增数据
mysql> create table test_tb2(id int,name varchar(30));
5. 从备份目录中xtrabackup_info文件获取到binlog和pos位置
# cat /var/lib/mysql/xtrabackup_info uuid = 201af9db-1fce-11e5-96b0-525400e4239d name = 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) (revision id: ) server_version = 5.5.43-0ubuntu0.12.04.1-log start_time = 2015-07-01 16:49:43 end_time = 2015-07-01 16:49:46 lock_time = 1 binlog_pos = filename 'mysql-bin.000001', position 429 #这个位置 innodb_from_lsn = 0 innodb_to_lsn = 1598188 partial = N incremental = N format = file compact = N compressed = N
6. 从库设置从这个日志点同步，并启动
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
......可以看到IO和SQL线程均为YES，说明主从配置成功。
9. 从库查看weibo库里面的表
mysql> show tables; +---------------------------+ | Tables_in_weibo | +---------------------------+ | test_tb |
发现刚才模拟创建的test_tb2表已经同步过来。
The above is the detailed content of Two ways to add slave databases without stopping the MySQL service. For more information, please follow other related articles on the PHP Chinese website!