Increase from library in two ways--do not stop Mysql service _mysql

General online increase from the library there are two ways, one is through the mysqldump backup of the main library, restore to from the library, mysqldump is a logical backup, the data volume is large, the backup speed will be very slow, lock the table time will be very long. The other is through the Xtrabackup tool to back up the main library, restore to from the library, Xtrabackup is a physical backup, backup speed, do not lock the table. Why not lock the table? Because it will monitor the main library log, if there are updated data, it will be written to a file, and then back to the backup file, so as to maintain data consistency.

Now the production environment MySQL database is a main one from, due to the increasing volume of business access, so add another from the library. The premise is not to affect the online business use, that is, can not restart the MySQL service, in order to avoid other situations, choose to visit the site at Low peak time period operation.

Server information:

Main Library: 192.168.18.212 (original)
From library 1:192.168.18.213 (original)
From library 2:192.168.18.214 (new)
Database version: MySQL5.5
Storage Engine: Innodb
Test Library Name: Weibo

First, Mysqldump Way

MySQL Master is based on Binlog log, so after the installation of the database will open Binlog. The advantage is that, on the one hand, you can restore the database with Binlog, on the other hand can prepare for the main.

The original main library configuration parameters are as follows:

# VI MY.CNF
Server-id = 1 #id要唯一
Log-bin = Mysql-bin #开启binlog日志
Auto-increment-increment = 1 #在Ubuntu系统中MySQL5.5 after the default is 1
Auto-increment-offset = 1
Slave-skip-errors = All #跳过主从复制出现的错误

1. Main Library Create Sync account

Mysql> Grant all in *.* to ' sync ' @ ' 192.168.18.% ' identified by ' sync ';

2. Configure MySQL from the library

# VI MY.CNF
Server-id = 3 #这个设置3
Log-bin = Mysql-bin #开启binlog日志
Auto-increment-increment = 1 #这两个参数在Ubuntu系统中MySQL5.5 after the default is 1
Auto-increment-offset = 1
Slave-skip-errors = All #跳过主从复制出现的错误

3. Back up the main library

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

Parameter description:
--routines: Exporting stored procedures and functions
--single_transaction: The transaction isolation state is set at the start of the export and the transaction is started with a consistent snapshot and then unlock tables, while lock-tables locks a table and cannot write until the dump is complete.
--master-data: The default equals 1, the dump start (change master to) Binlog Point and the POS value are written to the result, equal to 2 is to write the change master to the result and comment.

4. Copy the backup library to the library

# SCP Weibo.sql Root@192.168.18.214:/home/root

5. Create the TEST_TB table in the main library, simulate the database new data, Weibo.sql is not

Mysql> CREATE TABLE TEST_TB (ID int,name varchar (30));

6. Import a backup library from a library

# 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; #大概22行

8. From the library settings from this log point synchronization, 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: The 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 IO and SQL threads are all yes, indicating that the master-slave configuration was successful.

9. View the table inside the Weibo library from the library

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

found that the TEST_TB table created just now has been synchronized!

Second, Xtrabackup Way (recommended)

On the basis of the above configuration to do experiments, first remove from the library configuration:

mysql> stop Slave; #停止同步
mysql> Reset Slave; #清除从连接信息
Mysql> show Slave status\g; #再查看从状态, you can see that IO and SQL threads are no
mysql> drop database Weibo; #删除weibo库

At this time, from the library now and new clothes, like, continue to move!

1. Main Library using Xtrabackup backup

# Innobackupex--user=root--password=123.

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

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

2. Copy the backup directory to the library

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

3. Stop the MySQL service from the library, delete the DataDir directory, rename the backup directory to the DataDir directory

# 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. Create TEST_TB2 table in main library, simulate database new data

Mysql> CREATE TABLE TEST_TB2 (ID int,name varchar (30));

5. Get xtrabackup_info files from backup directory to Binlog and POS locations

# 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 the 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. From the library settings from this log point synchronization, 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
......

You can see that IO and SQL threads are all yes, indicating that the master-slave configuration was successful.

7. View the table inside the Weibo library from the library

Mysql> Show tables;
+---------------------------+
| Tables_in_weibo |
+---------------------------+
| TEST_TB |
| TEST_TB2 |

Discover that the TEST_TB2 table you just created has been synchronized.