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 &NBSP;&NBSP;4096&NBSP;JUL&NBSP;&NBSP;1&NBSP;16:49&NBSP;./DRWX------ 4 root root &NBSP;&NBSP;&NBSP;4096&NBSP;JUL&NBSP;&NBSP;1&NBSP;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