No-stop MySQL service case increase from the Library two common ways

Now the production environment MySQL database is a primary one from, because the traffic volume of access is increasing, 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 the site traffic low peak time period operation.

General online increase from the library there are two ways, one is through the mysqldump Backup 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. Another is to back up the main library through the Xtrabackup tool, restore to from the library, Xtrabackup is the physical backup, the backup speed is fast, does not lock the table. Why not lock the table? Because the main library log is monitored by itself, if there is updated data, it is written to a file and then returned to the backup file to maintain data consistency.

Server information:

Main Library: 192.168.18.212

From library 1:192.168.18.214

From library 2:192.168.18.214

Database version: MySQL5.5

Storage Engine: Innodb

Test Library Name: Weibo

First, Mysqldump Way

MySQL master-Slave is based on the Binlog log, so after installing the database will be opened Binlog. The advantage is that on the one hand you can restore the database with Binlog, on the other hand to prepare for the main.

The main library configuration parameters are as follows:

# vi My.cnfserver-id = 1 #id要唯一log-bin = mysql-bin #开启binlog日志auto-increment-increment = 1 #在Ubuntu系 The default is 1auto-increment-offset = 1 slave-skip-errors = all #跳过主从复制出现的错误 after MySQL5.5

1. Create a sync account with the main library

Mysql> Grant All on * * to ' sync ' @ ' 192.168.18.% ' identified by ' sync ';

2. Configure MySQL from the library

# VI My.cnfserver-id = 3 #这个设置3log-bin = mysql-bin #开启binlog日志auto-increment-increment = 1 #这两个参数在Ub MySQL5.5 in the Untu system has been 1auto-increment-offset since the default = 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, and Lock-tables is locked in a table that cannot be written 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 the change master to write to the result and comment.

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, simulate the database new data, Weibo.sql is not

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. 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. Sync from library settings 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 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 ...

You can see that both IO and SQL threads are Yes, indicating a successful master-slave configuration.

9. View the tables in the Weibo library from the library

Mysql> Show tables;

found that the TEST_TB table created by the simulation has been synchronized!

Second, Xtrabackup Way (recommended)

To do experiments based on the above configuration, remove the configuration from the library first:

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

At this point, from the library now and the same as the newly installed, continue to move!

1. Primary 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 18480drwxr-xr-x 5 root root      4096 JUL  1 16:49 ./DRWX------ 4 root root      4096 JUL  1 16:49&NBSP, .... /-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 library

# scp-r 2015-07-01_16-49-43 [email protected]:/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 a TEST_TB table in the main library to simulate new data in the database

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

5. Get the Xtrabackup_info file from the backup directory to the Binlog and POS locations

# 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    # This position innodb_from_lsn = 0innodb_to_lsn = 1598188partial = nincremental =  nformat = filecompact = ncompressed = n 

6. Sync from library settings 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=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 both IO and SQL threads are Yes, indicating a successful master-slave configuration.

9. View the tables in the Weibo library from the library

Mysql> Show tables;

found that the TEST_TB2 table just created by the simulation has been synchronized.

more xtrabackup Use View blog post:http://lizhenliang.blog.51cto.com/7876557/1612800

This article is from the "Li Zhenliang Technology Blog" blog, make sure to keep this source http://lizhenliang.blog.51cto.com/7876557/1669829

No-stop MySQL service case increase from the Library two common ways