Home>

Now the production environment mysql database is a master and a slave,As business traffic continues to increase,So add another slave from the library.The premise is that it cannot affect the use of online business,In other words, you cannot restart the mysql service. In order to avoid other situations,Choose to operate during periods of low website traffic

There are generally two ways to add slaves online,One is to backup the main library through mysqldump,Revert to slave library,mysqldump is a logical backup,When the amount of data is large,Backups can be slow,The lock time will also be very long.The other is to back up the main library through the xtrabackup tool.Revert to slave library,xtrabackup is a physical backup,Fast backups,Do not lock the table. Why not lock the table?Because it will monitor the main library log,If there is updated data,Will be written to a file first,Then return to the backup file,In order to maintain data consistency.

Now the production environment mysql database is a master and a slave,As business traffic continues to increase,So add another slave from the library.The premise is that it cannot affect the use of online business,In other words, you cannot restart the mysql service. In order to avoid other situations,Choose to operate during periods of low website traffic.

server information:

Main library:192.168.18.212 (previously)

From library 1:192.168.18.213 (previously)

From library 2:192.168.18.214 (new)

Database version:mysql5.5

Storage engine:innodb

Test library name:weibo

First, the mysqldump method

mysql master-slave is based on binlog logs, so you must enable binlog after installing the database. The advantage is thatOn the one hand, you can use binlog to restore the database.On the other hand, you can prepare for the master and slave.

The original main library configuration parameters are as follows:

#vi my.cnf

server-id=1 #id needs to be unique

log-bin=mysql-bin #Enable binlog logging

auto-increment-increment=1 #In ubuntu system mysql5.5 has defaulted to 1

auto-increment-offset=1

slave-skip-errors=all #Skip errors in master-slave replication

1. Create synchronization account in main library

2. Configure mysql from the library

#vi my.cnf

server-id=3 #this setting 3

log-bin=mysql-bin #Enable binlog logging

auto-increment-increment=1

auto-increment-offset=1

slave-skip-errors=all #Skip errors in master-slave replication

3. Back up 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 transaction isolation status at the beginning of the export,And start transactions using consistent snapshots,Then unlock tables;and lock-tables is a table that cannot be written.Until the dump is complete.

--master-data:The default value is 1. Write the dump master change binlog point and the pos value to the result.Equal to 2 is to write the change master to the result and comment.

4. Copy the backup repository to the slave repository

5. Create the test_tb table in the main database, and simulate the new data in the database.weibo.sql is not

mysql>create table test_tb (id int, name varchar (30));

6. Import backup library from library

#mysql -uroot -p123 -e "create database weibo;"

#mysql -uroot -p123 weibo<weibo.sql

7. View the 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;#about 22 lines

8. Synchronize 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 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 |

It was found that the test_tb table just created by the simulation has been synchronized!

Second, xtrabackup method (recommended)

Experiment based on the above configuration,First delete the slave configuration:

mysql>stop slave;#stop synchronization

mysql>reset slave;#clear slave connection information

mysql>show slave status \ g;#View slave status again,You can see that both the io and sql threads are no

mysql>drop database weibo;#delete weibo library

At this point, the slave library is now the same as the new one,Keep going!

1. The main library is backed up using xtrabackup

#innobackupex --user=root --password=123 ./

Generate a backup directory named by time: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. Copy the backup directory to the slave

3. Stop the mysql service from the library,Delete the datadir directory and 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 #Check that it has started normally

mysql 8832 1 0 16:55?00:00:00/usr/sbin/mysqld

4. Create the test_tb2 table in the main database, and add new data to the simulated database

mysql>create table test_tb2 (id int, name varchar (30));

5. Obtain the binlog and pos locations from the xtrabackup_info file in the backup directory

#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 #this position

innodb_from_lsn=0

innodb_to_lsn=1598188

partial=n

incremental=n

format=file

compact=n

compressed=n

6. Synchronize from the 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 the io and sql threads are 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 |

It was found that the test_tb2 table created just now has been synchronized.

  • Previous Solve the problem of jquery_ujs component slowing down in Ruby on Rails
  • Next Sina Weibo lobby text content scroll effect code implemented by JS