2-16 MySQL master-slave replication

2-16 MySQL master-slave replication 1. Deploy MySQL master-slave sync <M-S>

Environment: MySQL version consistent, all 5.7.18

Master XUEGOD4 IP 192.168.10.34 Database Password yourpasswd

Slave xuegod5 IP 192.168.10.35 database Password yourpasswd

1.1 Configure the primary database xuegod41.1.1 create the databases that need to be synchronized:

mysql> CREATE DATABASE HA;

mysql> use HA;

Mysql> CREATE TABLE T1 (ID int,name varchar (20));

Service Mysqld Stop

1.1.2 Configuration My.cnf

Vim/etc/my.cnf

Log-bin=mysql-bin-master #启用二进制日志

Server-id=1 #本机数据库ID Mark

Binlog-do-db=ha #可以被从服务器复制的库. The name of the database in which the binary needs to be synchronized

Binlog-ignore-db=mysql #不可以被从服务器复制的库

1.1.3 Restart MySQL

Service mysqld Restart

1.1.4 Authorization

mysql> grant replication Slave on * * to [e-mail protected] identified by "123456";

Note: If the error says that the password is too simple, you can change the password policy

Mysql> Set Global validate_password_policy=0

Refresh Permissions after authorization:

mysql> flush Privileges;

1.1.5 Viewing status information

Mysql> Show master status;

1.1.6 Viewing binary logs

View MySQL Data Catalog, yum mode installation data directory for/var/lib/mysql

[Email protected] ~]# Ls/var/lib/mysql

Mysql> Show Binlog Events\g

Ensure that the synchronized database is consistent prior to replication

[Email protected] ~]# mysqldump-uroot-pyourpasswd HA >ha.sql #可以导出数据库

To pass the exported database to the slave server, method:

[Email protected] ~]# SCP ha.sql 192.168.10.35:/root/

1.2 Configuration from database server xuegod51.2.1 two database server MySQL version to be consistent

Mysql> Show variables like '%version% ';

1.2.2 Test whether connecting to the primary server is successful

[Email protected] ~]# mysql-uslave-pyourpasswd-h 192.168.10.34

The HA database is not visible because only copy permissions are not read.

1.2.3 Import Database, consistent with primary database server

mysql> CREATE DATABASE HA;

[Email protected] ~]# mysql-uroot-pyourpasswd ha

1.2.4 Modifying the configuration file from the server

No need to open Bin-log log from server

[[Email protected] ~]# service mysqld stop

[Email protected] ~]# VIM/ETC/MY.CNF

master-host=192.168.10.34 #指定主服务器IP地址

Master-user=slave #指定定在主服务器上可以进行同步的用户名

MASTER-PASSWORD=YOURPASSWD #密码

MySQL version is less than version 5.7 to configure the above three sentences. Version 5.7 only needs to be configured:

server-id=2 #从服务器ID号, do not be the same as the primary ID, if you set multiple slave servers, each slave server must have a unique Server-id value that must be different from the primary server and the other from the server.

Server-id values can be considered similar to IP addresses: These ID values uniquely identify each server instance in the replication server cluster.

1.2.5 configuration from the database server

[Email protected] ~]# service mysqld Restart #重启mysql服务

Mysql>stop slave; #停止slave

mysql> Change Master to master_host= ' 192.168.10.34 ', master_user= ' slave ', master_password= ' yourpasswd ';

mysql> start slave; #启动slave

Mysql> Show Slave Status\g #查看状态

slave_io_running : one responsible for IO communication with the host

slave_sql_running : Responsible for your own slave MySQL process

Then view the status on the primary server:

Mysql> Show Processlist \g

1.3 Inserting Data Test synchronization

To insert data on the primary database server:

mysql> INSERT into T1 value (6, ' Yehailun ');

Query OK, 1 row affected (0.05 sec)

Querying from the database server

Mysql> select * from T1;

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

| ID | name |

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

| 6 | Yehailun |

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

1 row in Set (0.00 sec)

1.4 Row fault

If you encounter the master never synchronizes, look at the location of the master-slave Bin-log, and then synchronize.

See a list of binary log events on the primary server

Mysql> Show Binlog Events \g

Execute the MySQL command from the server:

mysql> stop Slave;

Query OK, 0 rows affected (0.03 sec)

mysql> Change Master to master_log_file= ' mysql-bin-master.000001 ', master_log_pos=1117;

Query OK, 0 rows affected (0.04 sec)

# based on the results of the show master status of the primary server above, the binary database records from the server are returned to achieve the effect of synchronization.

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

If all is yes, it means that the synchronization

Restart the server, and then view the status:

Stop slave stop from the server;

Open from server slave start;

Troubleshooting ideas:

1. The binary log is not turned on

2, IPTABLES not release the port

3. The corresponding host IP address was incorrectly written

SQL thread Error

1, the master-slave server database structure is not unified

After the error, the data is small, you can manually resolve the creation of the insert, and then update the slave state.

Note: If the Lord mistakenly deleted it. Then it was mistakenly deleted from the top. # So the Lord will make regular mysqldump backups.

2. Deploy MySQL main master bidirectional master-slave replication m-m (resume snapshot re-experiment)

MySQL Master: Configuration for MySQL bidirectional synchronous database ha

MySQL Master: server: Xuegod4 ip:192.168.10.34

MySQL Master: server: Xuegod5 ip:192.168.10.35

Clear the binary log first,mysql> reset Master

Configuration based on the above master-slave

2.1 Creating a database that needs to be synchronized

mysql> CREATE DATABASE HA;

mysql> use HA;

Mysql> CREATE TABLE T1 (ID int,name varchar (20));

2.2 Configuring XUEGOD4

It has two types of identities:

Identity 1:xuegod5 the master, identity 2:xuegod5 from.

2.2.1 Modifying a configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

Server-id = 1

Log-bin=mysql-bin-master

Binlog-do-db=ha

Binlog-ignore-db=mysql #避免同步mysql用户相关配置

2.2.2 Restart MySQL Service

[Email protected] ~]# service mysqld restart

Check Status

Mysql> Show master status;

2.2.3 authorized to Xuegod5 do from

Modify Password Policy

mysql> set global validate_password_policy=0;

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.10.35 ' identified by ' yourpasswd ';

If you do not meet the password requirements of the error changes as follows (5.7 version)

mysql> set global validate_password_policy=0; #定义复杂度

mysql> set global validate_password_length=1; #定义长度 Default is 8

Refresh Permissions

mysql> flush Privileges;

2.3 Configuring xuegod52.3.1 Modifying configuration Files

Configuration of MySQL 5.1

master-host=192.168.1.64 #指定主服务器IP地址

Master-user=slave64 #指定定在主服务器上可以进行同步的用户名

master-password=123456 #密码

#master-port=3306 can not write

Replicate-do-db=ha #要复制的数据库

Master-connect-retry=60 #断点重新连接时间

MySQL 5.7 configuration

[Email protected] ~]# VIM/ETC/MY.CNF

server-id=2

Log-bin=master-bin-slave

Binlog-do-db=ha

Binlog-ignore-db=mysql

2.3.2 Restart MySQL Service

[Email protected] ~]# service mysqld restart

View status;

Mysql> Show master status;

2.3.3 Test the login from the account is normal

[Email protected] ~]# mysql-uslave-pyourpasswd-h 192.168.10.34

MySQL: [Warning] Using a password on the command line interface can is insecure.

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

Your MySQL Connection ID is 5

Server Version:5.7.18-log MySQL Community Server (GPL)

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>

2.3.4 authorized to XUEGOD4 do from

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.10.34 ' identified by ' yourpasswd ';

If you do not meet the password requirements of the error changes as follows (5.7 version)

mysql> set global validate_password_policy=0; #定义复杂度

mysql> set global validate_password_length=1; #定义长度 Default is 8

Refresh Permissions

mysql> flush Privileges;

2.4 Specifying the master of the XUEGOD5

mysql> Change Master to master_host= ' 192.168.10.34 ', master_user= ' slave ', master_password= ' yourpasswd ';

mysql> start slave;

Mysql> Show Slave Status\g

2.5 Specifying the master of the XUEGOD4

mysql> Change Master to master192.168.10.35 ', master_user= ' slave ', master_password= ' yourpasswd ';

mysql> start slave;

View the status of slave on 2.6 xuegod4

Mysql> Show Slave Status\g

View the status of slave on 2.7 xuegod5

Mysql> Show Slave Status\g

2.8 Inserting data test 2.8.1 inserting data on xuegod4, viewing on XUEGOD5

Mysql> Use HA

Database changed

mysql> INSERT INTO T1 values (1, ' Zhangsan ');

Query OK, 1 row affected (0.02 sec)

2.8.2 inserting data on xuegod5, viewing on XUEGOD4

mysql> INSERT into T1 value (2, ' Lisi ');

Query OK, 1 row affected (0.06 sec)

Note: This M-M architecture has no advantage, he often gives us an illusion of load balancing

3 Deploying the M-S-S model

Environment:

XUEGOD4 Master mysql5.7.18 192.168.10.34

XUEGOD5 slave relay MYSQL5. 7.18 192.168.10.35

Xuegod6 slave mysql5. 7.18 192.168.10.36

3.1 Creating a database that needs to be synchronized

mysql> CREATE DATABASE HA;

mysql> use HA;

Mysql> CREATE TABLE T1 (ID int,name varchar (20));

3.2 Deployment of Master xuegod43.2.1 authorization

To modify a password policy:

mysql> set global validate_password_policy=0;

To authorize a user on the primary service:

mysql> grant replication Slave on * * to ' REPL ' @192.168.10.35 identified by ' yourpasswd ';

Refresh permissions:

mysql> flush Privileges;

3.2.2 Modifying configurations

[Email protected] ~]# VIM/ETC/MY.CNF

Server-id = 1

Binlog-do-db = HA

Log-bin=mysql-bin-master

Binlog-ignore-db=mysql

Sync-binlog=1

Binlog-format=row

Restart Service:

[Email protected] ~]# service mysqld restart

3.2.3 Export master server ha full backup, copy to trunk and slave

[[email protected] ~]# mysqldump-uroot-pyourpasswd ha-b>ha.sql #使用-B When importing a database without creating a new library

[Email protected] ~]# SCP ha.sql 192.168.10.35:/root

[Email protected] ~]# SCP ha.sql 192.168.10.36:/root

3.3 Deployment of slave relay xuegod53.3.1 Import Database

mysql> CREATE DATABASE HA;

[Email protected] ~]# mysql-uroot-pyourpasswd ha

3.3.2 Modifying configurations

[Email protected] ~]# VIM/ETC/MY.CNF

server-id=2

Binlog-do-db=ha

Log-bin=mysql-bin-slave1

Binlog-ignore-db=mysql

Binlog-format=row

Log-slave-updates=1 #把它从relay The binary logs that are read in the-log and the operations performed on this machine are also recorded in this own binary log so that the third slave can read the corresponding data changes through the relay slave

Restart Service:

[Email protected] ~]# service mysqld restart

3.3.3 Authorization

mysql> Change Master to master_host= ' 192.168.10.34 ', master_user= ' repl ', master_password= ' yourpasswd ';

mysql> start slave;

Check the status:

Authorize a user to Slave (XUEGOD6)

mysql> set global validate_password_policy=0; #修改密码策略

mysql> grant replication Slave on * * to ' REPL ' @192.168.10.36 identified by ' yourpasswd ';

Refresh Permissions

mysql> flush Privileges;

3.3.4 Exporting a database

[Email protected] ~]# Mysqldump-uroot-pyourpasswd-b ha>ha2.sql

Send To Xuegod6

3.4 Deployment of slave xuegod63.4.1 import data

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

3.4.2 Modifying configurations

[Email protected] ~]# VIM/ETC/MY.CNF

Server-id=3

Log-bin=mysql-bin-slave2

Binlog-format=row

Restart Service:

[Email protected] ~]# service mysqld restart

3.4.3 Specifies the slave trunk as the primary

mysql> Change Master to master_host= ' 192.168.10.35 ', master_user= ' repl ', master_password= ' yourpasswd ';

mysql> start slave;

3.5 Inserting data tests from master

Mysql> CREATE TABLE Mermber (ID int (4) unsigned NOT NULL auto_increment,name varchar (), primary key (ID));

mysql> INSERT INTO Mermber values (1, ' Day of the Slaughter '), (2, ' lone leaf ');

2-16 MySQL master-slave replication