Slave used as Master of other slave

Points to note for master-slave configuration

(1) The master-slave server operating system version and the same number of bits;

(2) The version of master and slave database should be consistent;

(3) The data in the master and slave databases should be consistent;

(4) Master Open binary log, master and slave server_id must be unique within the LAN;

Brief steps for Master-slave configuration

1, the configuration on master

(1) Installation database;

(2) Modify the database configuration file, indicate server_id, open binary log (log-bin);

(3) Start the database to see which log is currently, what is the position number;

(4) Login database, authorized data Copy user (IP address is slave IP address, if it is two-way master and slave, here also need to authorize the IP address of this machine, at this time own IP address is from IP address);

(5) Back up the database (remember to lock and unlock);

(6) transmitting the backup data to the slave;

(7) Start the database;

The following steps, for one-way master-Slave build success, want to build two-way master-slave required steps:

(1) Log in the database, specify the address of master, user, password and other information (this step is only two-way master and slave needs);

(2) Turn on the synchronization, check the status;

2, slave on the configuration

(1) Installation database;

(2) Modify the database configuration file, indicate server_id (if it is to build two-way master and slave, also to open the binary log log-bin);

(3) Start the database, restore the backup;

(4) Check which log is currently, what is the position number (one-way master from this step does not need, bi-directional master and slave needs);

(5) Specify the address, user, password and other information of master;

(6) Turn on sync to view status.

One-way master-slave environment (also known as MySQL A/b replication) build

1, Master (192.168.1.205) and slave (192.168.1.206) are installed on the same version of the database (mysql-5.6.26.tar.gz), refer to the High-Availability Architecture-section 13th--mysql Source compilation installation (centos6.6+mysql5.6).

Note: The SELinux for both database servers must be disable (permanently off selinux, please modify/etc/selinux/config to change SELinux to Disabled)

2. Modify the master configuration file/etc/my.cnf

[[email protected] ~]# VI/ETC/MY.CNF # # Add the following configuration entry in [Mysqld]

# # Set SERVER_ID, generally set to IP

server_id=205

# # Copy Filter: The database that needs to be backed up, output binlog

#binlog-do-db=roncoo

# # Copy Filter: No backup of the database, no output (MySQL library is not generally synchronized)

Binlog-ignore-db=mysql

# # Turn on the binary log function, you can take whatever you want, preferably with meaning

Log-bin=edu-mysql-bin

# # Memory allocated for each session, used to store the cache of binary logs during a transaction

binlog_cache_size=1m

# # Master-slave copy format (mixed,statement,row, default format is statement)

Binlog_format=mixed

# # Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted.

Expire_logs_days=7

# # Skip all errors encountered in master-slave replication or specify types of errors to avoid slave-side replication interrupts.

# # Example: 1062 error refers to some primary key duplication, 1032 error is due to master and slave database data inconsistency

slave_skip_errors=1062

(If you want to know more detailed analysis of the above parameters, we can directly Baidu parameter name)

2.1 Replication filtering allows you to replicate only a subset of the data in the server, and there are two types of replication filtering:

(1) Filtering the events in the binary log on master;

(2) Filter the events in the relay log on the slave. As follows:

2.2 MySQL replication type for binary log (Binlog)

(1) Statement-based replication: The SQL statement executed on master executes the same statement on slave. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.

(2) Row-based replication: Copy the changed content to slave instead of executing the command on the slave. Support Starts from MySQL5.0.

(3) Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

3. Start/Restart the master database service, log in to the database, create a data synchronization user, and grant the appropriate permissions

[[Email protected] ~]# service mysql restart shutting down MySQL. [OK] Starting MySQL. [OK] [Email protected] ~]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.6.26-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

# #创建数据同步用户, and grant the appropriate permissions

mysql> grant replication Slave, replication Client on *. * to ' repl ' @ ' 192.168.1.206 ' identified by ' roncoo.123 ';

Query OK, 0 rows Affected (0.00 sec)

# # Refresh Authorization table information

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

# # Check the position number and make a note of the position number (this position and now log files are required from the machine)

Mysql> Show master status;

4, create Roncoo Library, table, and write a certain amount of data, used to simulate the existing business system database

Create database if not EXISTS roncoo default charset UTF8 collate utf8_general_ci;

Use Roncoo;

DROP TABLE IF EXISTS ' Edu_user ';

CREATE TABLE ' Edu_user ' (

' Id ' int (one) not NULL auto_increment,

' UserName ' varchar (255) Not NULL DEFAULT ' COMMENT ' username ',

' pwd ' varchar (255) Not NULL DEFAULT ' COMMENT ' password ',

PRIMARY KEY (' Id ')

) Engine=innodb auto_increment=2 DEFAULT charset=utf8 comment= ' user Information table ';

INSERT into ' Edu_user ' VALUES (1, ' Wu Shui ', ' 123456 '), (2, ' Breeze ', ' 123456 '), (3, ' Dragon fruit ', ' roncoo.com ');

5, in order to ensure that the master and slave data consistent, we use the primary backup, from the restore to achieve the initial data consistency

# # First Temporary lock table

Mysql> flush tables with read lock;

Query OK, 0 rows Affected (0.00 sec)

# # Here we implement a full-database backup, in practice, we may only synchronize one library, that can also be backed up only one library

[Email protected] ~]# mysqldump-p3306-uroot-p--add-drop-table roncoo >/tmp/edu-master-roncoo.sql;

Warning:using a password on the command line interface can is insecure.

Enter Password:

[Email protected] ~]# cd/tmp [[email protected] tmp]# LL

Total 644

-rw-r--r--1 root root 644266 Dec 04:10 edu-master-roncoo.sql

Note: In the actual production environment of large data volume (Ultra 2G data) backup, it is recommended not to use Mysqldump to score, because it will be very slow. It is recommended to use Xtrabackup for backup.

# # Ching

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

Remote transfer of data backed up on master to slave for data recovery at slave configuration

[Email protected] ~]# scp/tmp/edu-master-roncoo.sql [email protected]:/tmp/

[email protected] ' s password:

Edu-master-roncoo.sql 100% 629KB 629.2kb/s 00:00

[Email protected] ~]#

6, Next processing slave (192.168.1.206), configuration files only need to modify one item, the rest of the configuration with commands to operate

[[email protected] ~]# VI/ETC/MY.CNF # # Add the following configuration entry in [Mysqld]

# # Set SERVER_ID, generally set to IP

server_id=206

# # Copy Filter: The database that needs to be backed up, output binlog

#binlog-do-db=roncoo

# #复制过滤: Do not need to back up the database, not output (MySQL library is not generally synchronized)

Binlog-ignore-db=mysql

# # Turn on the binary log for slave to be used as the master of other slave

Log-bin=edu-mysql-slave1-bin

# # Memory allocated for each session, used to store the cache of binary logs during a transaction

Binlog_cache_size = 1M

# # Master-slave copy format (mixed,statement,row, default format is statement)

Binlog_format=mixed

# # Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted.

Expire_logs_days=7

# # Skip all errors encountered in master-slave replication or specify types of errors to avoid slave-side replication interrupts.

# # Example: 1062 error refers to some primary key duplication, 1032 error is due to master and slave database data inconsistency

slave_skip_errors=1062

# # Relay_log Configuring the trunk log

Relay_log=edu-mysql-relay-bin

# # Log_slave_updates means slave writes the copy event into its own binary log

Log_slave_updates=1

# # prevents changing data (except for special threads)

Read_only=1

If slave is a master of another slave, you must set the Bin_log. Here we turn on the binary log, and explicitly name it (the default name is hostname, but if the hostname changes there will be a problem).

Relay_log configures the trunk log, log_slave_updates indicates that slave writes the replication event into its own binary log.

When setting log_slave_updates, you can let slave play the Master of other slave. At this point, slave writes the events executed by the SQL thread to its own binary log (binary logs), and then its slave can get the events and execute it. As shown (sending replication events to other slave):

7. Restart the MySQL service after saving, restore the backup data

[[Email protected] ~2881064151]# service mysql restart shutting down MySQL. [OK] Starting MySQL. [OK]

Create the same library on slave:

Create database if not EXISTS roncoo default charset UTF8 collate utf8_general_ci;

Use Roncoo;

Import data

[Email protected] ~]# Mysql-uroot-p Roncoo </tmp/edu-master-roncoo.sql

Enter Password:

[Email protected] ~]#

8, login Slave database, add relevant parameters

(Master IP, port, sync user, password, position number, which log file to read)

[Email protected] ~]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 3

Server Version:5.6.26-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Change Master to master_host= ' 192.168.1.205 ', master_user= ' repl ', master_password= ' roncoo.123 ', master_port =3306, master_log_file= ' edu-mysql-bin.000001 ', master_log_pos=1389, master_connect_retry=30;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Explanation of the command executed above:

Master_host= ' 192.168.1.205 ' # # Master's IP address

Master_user= ' Repl ' # # users for synchronizing data (authorized users in master)

Master_password= ' roncoo.123 ' # # Sync data user's password

master_port=3306## the port of the master database service

Master_log_file= ' edu-mysql-bin.000001 ' # #指定Slave从哪个日志文件开始读复制数据 (you can see the log file name using show Master status on Master)

master_log_pos=429## 2881064151 starting from which position to read

master_connect_retry=30# #当重新建立主从连接时, if the connection establishment fails, how often after the interval is retried. The unit is seconds, the default setting is 60 seconds, and the synchronization delay tuning parameter.

# # View Master-Slave synchronization status

Mysql> show Slave status\g;

You can see that slave_io_state is empty, and slave_io_running and slave_sql_running are no, indicating that Slave has not started the copy process.

# # Turn on master-slave synchronization

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

Slave used as Master of other slave