MySQL master-slave replication and primary master replication

MySQL master-slave replication and primary master replication
1. About MySQL
MySQL is a relational database management system, where relational databases store data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.
The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software has adopted a dual licensing policy, divided into community and commercial version, due to its small size, fast, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database.
The single MySQL database server does not meet the actual requirements. At this point the database cluster is a good solution to this problem. With MySQL distributed cluster, you can build a high-concurrency, load-balanced cluster Server (this blog is not involved for the time being). Prior to this we had to ensure that the data in each MySQL server was synchronized. Data synchronization we can easily complete with MySQL internal configuration, mainly with master-slave replication and primary master replication.
2. Construction of basic Environment:
2.1 Environmental Description
ip:192.168.92.143
ip:192.168.92.156
CentOS 7.4.1708
MySQL 5.7.22

2.2 Setting hostname, static interpretation:/etc/hosts
2.3 SSH Password-free login
SSH-KEYGEN-T RSA
Ssh-copy-id-i. Ssh/id_rsa.pub 192.168.92.143
2.4 NTP synchronization
Yum Install Chrony-y
Edit/etc/chrony.conf File
Server Master Iburst
Allow 192.168.92.0/24
Systemctl Enable Chronyd.service
Systemctl Start Chronyd.service
Chronyc sources
2.5 Install the database:
(due to the use of the company script, post-update MySQL installation)
3. mysql Master-slave replication
3.1 Configuration Files
MySQL configuration files in general Linux are all in/ETC/MY.CNF (the configuration file in Windows is Mysql.ini)
Log-bin=mysql-bin #开启二进制日志
server_id=1# two servers with the same ID.
Note: The binary log must be turned on because the synchronization of the data is essentially the other MySQL database server executes the binary log of this data change again on this computer.
192.168.92.143 Primary database server
Log-bin=mysql-bin
Server_id=1
192.168.92.156 from the database server
Log-bin=mysql-bin
server_id=2
Remember to restart the service after the configuration is complete
Systemctl Restart Mysqld
3.2 Start building master-slave replication
The first step:
Create a MySQL user in 192.168.92.143 that can log on in a 192.168.92.156 host
User: Backup
Password: 1234
Mysql>grant Replication Slave on.To ' backup ' @ ' 192.168.92.156 ' identified by ' 1234 ';
Mysql>flush privileges;
Step Two:
View 192.168.92.143MySQL server binary file name and location
Mysql>show MASTER STATUS;

       第三步：        告知二进制文件名与位置        在192.168.92.156中执行：

mysql> Change Master to master_host= ' 192.168.92.143 ', master_user= ' backup ', master_password= ' 1234 ', master_log_ File= ' mysql-bin.000003 ', master_log_pos=604;
Complete master-Slave replication configuration
3.3 Testing Master-slave replication
In the 192.168.92.156
Mysql>show SLAVE status\g #查看主从复制是否配置成功

When you see Slave_io_running:yes and Slave_sql_running:yes, it shows the state is normal.

        实际测试：

--Login 192.168.92.143 main MySQL
Mysql>show DATABASES;

--Landing 192.168.92.156 from MySQL
Mysql>show DATABASES;

192.168.92.143 Main MySQL operation:

        mysql>create database aa;        mysql>use aa;        mysql>create table tab1(id int auto_increment,name varchar(10),primary key(id));       mysql>show databases;       mysql>show tables;

192.168.92.156 from MySQL operation:
Mysql>show databases;
Mysql>use AA;
Mysql>show tables;

From the above two results graph can be learned that the two hosts to achieve data synchronization. The configuration of master-slave replication is so simple.
4. mysql Master replication
4.1 Implementation principle
Primary master replication can change the data within both MySQL hosts, and the other host will make changes accordingly. Smart you may have thought of how it should be done. Yes, it would be good to combine two master-slave replication organically together. But in the configuration, we need to pay attention to some problems, for example, primary key duplication, Server-id can not be repeated and so on.
4.2 Configuration Files
--192.168.92.143
Server-id=1 #任意自然数n, just ensure that the two MySQL hosts do not repeat.
Log-bin=mysql-bin #开启二进制日志
auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n
Auto_increment_offset=1 #起始值. Generally fill the nth master MySQL. This is the first master MySQL
Binlog-ignore=mysql #忽略 (or out of sync) MySQL library "I don't usually write."
Binlog-ignore=information_schema #忽略 (or out of sync) information_schema Library "I don't usually write."
Replicate-do-db=aa #要同步的数据库, all libraries by default
--192.168.92.156
server-id=2
Log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
Replicate-do-db=aa

       配置好后重启MySQL

4.3 Start building Primary master replication
Because primary master replication is a combination of two master-slave replication, I then proceed to the above master-slave replication and then configure.
The first step:
Create a MySQL user in 192.168.92.156 that can log on in a 192.168.92.143 host
Users: Backup2
Password: 1234
Mysql>grant replication Slave on . to ' backup2 ' @ ' 192.168.92.143 ' identified by ' 1234 ';
Mysql>flush privileges;
Step Two:
View 192.168.92.156MySQL server binary file name and location
Mysql>show Master status;

        第三步：       告知二进制文件名与位置       

mysql> Change Master to master_host= ' 192.168.92.156 ', master_user= ' backup2 ', master_password= ' 1234 ', master_log_ File= ' mysql-bin.000007 ', master_log_pos=615;

Completing the primary master replication configuration
Restart MySQL Service
4.4 Test Primary master replication
Log in to MySQL view:
Mysql>show SLAVE status\g #查看主从复制是否配置成功
192.168.92.143

When you see Slave_io_running:yes and Slave_sql_running:yes, it shows the state is normal.
Test:
--192.168.92.143

       mysql>use aa;       mysql>select*from tab1;       tab1无数据       --192.168.92.156       mysql>use aa;       mysql>select*from tab1;       tab1无数据       --192.168.92.1443插入数据       mysql>insert into tab1 (name) value(‘11’),(‘11’),(‘11’);       --192.168.92.156插入数据       mysql>insert into tab1 (name) value (‘12‘),(‘12‘),(‘12‘);

View data:
Two host data results as well!

5. Precautions
5.1 Common error points:
(1) The DB database exists in both databases, and the first MySQL DB has tab1, and the second MySQL DB does not have TAB1, that must not be successful.
(2) The binary log name and location of the data have been obtained, and the data operation has been done, causing the POS to be changed. The previous POS was used when configuring change master.
(3) After the stop slave, the data changes, then start slave. Error. This step can be omitted, but remember to restart the service.
Ultimate Correction: Re-run the change master again.
(4) When the configuration is complete slave_io_running, slave_sql_running is not all yes, most of the problems are caused by data disunity. Restart the service and check again.
5.2mysql Error Code (MEMO):
1005: Failed to create table
1006: Failed to create database
1007: Database already exists, database creation failed
1008: Database does not exist, delete database failed
1009: Unable to delete database file causes database failure to be deleted
1010: Unable to delete data directory causes database failure to be deleted
1011: Failed to delete database file
1012: Cannot read records in system tables
1020: Record has been modified by another user
1021: Insufficient space on the hard drive, please increase the hard disk free space
1022: keyword Repeat, change record failed
1023: An error occurred while shutting down
1024: Read File error
1025: An error occurred while changing the name
1026: Write File Error
1032: Record does not exist
1036: The data table is read-only and cannot be modified
1037: Insufficient system memory, please restart the database or restart the server
1038: Insufficient memory for sorting, increase the sort buffer
1040: The maximum number of connections to the database has been reached, please increase the number of available connections to the database
1041: Insufficient system memory
1042: Invalid host name
1043: Invalid connection
1044: The current user does not have permission to access the database
1045: Unable to connect to database, user name or password error
1048: field cannot be empty
1049: Database does not exist
1050: Data table already exists
1051: Data table does not exist
1054: field does not exist
1065: Invalid SQL statement, SQL statement is empty
1081: Unable to establish socket connection
1114: The data sheet is full and cannot hold any records
1116: Too many Open data tables
1129: Database is abnormal, please restart database
1130: Connection to database failed with no permissions to connect to database
1133: Database user does not exist
1141: The current user is not authorized to access the database
1142: The current user does not have permission to access the data table
1143: The current user does not have permission to access fields in the datasheet
1146: Data table does not exist
1147: User access to the data table is undefined
1149:sql statement Syntax error
1158: Network error, read error, please check network connection status
1159: Network error, read timeout, check network connection status
1160: Network error, write error, please check network connection status
1161: Network error, write timeout, please check network connectivity status
1062: Duplicate field value, inbound failed
1169: Duplicate field value, update record failed
1177: Open Data table failed
1180: Commit TRANSACTION failed
1181: ROLLBACK TRANSACTION failure
1203: The current user and database establish a connection that has reached the maximum number of connections to the database, increase the number of available database connections or restart the database
1205: Lock timeout
1211: The current user does not have permission to create a user
1216: FOREIGN KEY constraint check failed, update child table record failed
1217: FOREIGN KEY constraint check failed, delete or modify master table record failed
1226: The current user is using more resources than allowed, please restart the database or restart the server
1227: Insufficient permissions, you do not have permission to do this
1235:mysql version is too low to have this feature

MySQL master-slave replication and primary master replication