Do not stop the MySQL service by adding two ways from the library

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 (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-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 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 is already the default is 1

Auto-increment-offset = 1

Slave-skip-errors = All #跳过主从复制出现的错误

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.CNF

Server-id = 3 #这个设置3

Log-bin = Mysql-bin #开启binlog日志

Auto-increment-increment = 1 #这两个参数在Ubuntu系统中MySQL5.5 is already 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, 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 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 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;

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

| Tables_in_weibo |

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

| TEST_TB |

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 no

mysql> 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 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 1 16:49 xtrabackup_binlog_info

-rw-r-----1 root root 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_TB2 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-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. 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;

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

| Tables_in_weibo |

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

| TEST_TB |

| TEST_TB2 |

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

Free pick up brother even it education original Linux OPS engineer video/Detailed Linux tutorials, details Inquiry official website customer Service: http://www.itxdl.cn/linux/

Learn PHP, Linux, HTML5, UI, Android and other video tutorials (Courseware + notes + video)! Contact Q2430675018

Do not stop the MySQL service by adding two ways from the library