MySQL Master-Slave synchronization

Environment: centos7.3 mysql5.7

Main mysql:192.168.1.71

From mysql:192.168.1.72

Download the MYSQL5.7 RPM package on both servers in order to install it sequentially

RPM-IVH mysql-community-common-5.7.22-1.el7.x86_64.rpm
RPM-IVH mysql-community-libs-5.7.22-1.el7.x86_64.rpm
RPM-IVH mysql-community-client-5.7.22-1.el7.x86_64.rpm
RPM-IVH mysql-community-server-5.7.22-1.el7.x86_64.rpm

When MySQL is installed, an administrator password is written to the error log file the first time it is started.

Initialize using the Mysql_secure_installation script. When you're done, MySQL is ready to install.

Here I stole a lazy, only the primary server was initialized with a script, not initialized from the server. Direct copy of the main server configuration and database files, resulting in the back to dig a small hole.

First to create a new account on the main server, and give REPLICATION Savel, REPLICATION client permissions, for master-slave synchronization, of course, directly with the root account is also possible.

Now start configuring the primary server:

Edit/ETC/MY.CNF

[Mysqld]datadir=/var/lib/mysql/#mysql数据库文件位置socket=/var/lib/mysql/Mysql.sock #Unix套接字位置symbolic-links=0innodb_file_per_table=on# Open InnoDB one file per table Skip_name_resolve=on# Skip domain name reverse check server_id=1#服务器ID (globally unique) Log_bin=master-bin #开启logbin日志, and set the file name Binlog_format=mixed #二进制文件记录格式, MIXED mixed mode. Default to Row
#sync_binlog=1#同步写入binlog日志, turn on will reduce MySQL performance, default is 1#innodb_flush_log_at_trx_commit=1Data #开启innodb每次事务提交时MySQL都会把log buffer is written to logfile, the safest, the default is 1#innodb_support_xa=on# default to On[mysqld_safe]log-error=/var/log/mysql/Mysql.logpid-file=/var/run/mysql/mysql.pid

If there are some databases that do not want to be synchronized such as MySQL, Sys, Informaion_schema, Performance_schema
Can be set in the configuration file.
Binlog_ignore_db=mysql
Binlog_ignore_db=sys
... such as
Each database is set to one so that it is not recorded in the Binlog log, and of course it will not be synchronized.

To restart the primary server MySQL service:

Systemctrl Restart Mysqld

To configure the slave server:

Edit/ETC/MY.CNF

[Mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.socksymbolic-links=0  innodb_file_per_table=onskip_name_resolve=onserver_id=2# Global unique server ID number relay_log=relay-log# start the relay log feature, and set the file name Relay_log_index=relay-log.index# Set Intermediate log index file names
#skip_slave_start =on #服务启动时不自动启动复制线程
Read_only=on #设置从服务器只读

[Mysqld_safe]log-error=/var/log/mysql/mysql.logpid-file=/var/run/mysql/mysql.pid

Restart the MySQL service from the server:

Systemctrl Restart Mysqld

Use the MySQL client to log in to execute related commands from the server:

MySQL > Change MASTER to master_host= ' 192.168.1.71 ', master_user= ' root ', master_password= '! Qaz2wsx ', master_port=3306, master_log_file= ' master-bin.000005 ', master_log_pos=154;

mysql > START SLAVE;

The master-slave synchronization has been completed.

Note: Here's master_log_file= ' master-bin.000005 ', master_log_pos=154; must be the current Master_log_file and POS on the primary server. That is, the server will determine from which node to start synchronization with the primary server. MySQL client can log into the master server query

Mysql> Show master status; +-------------------+----------+--------------+------------------+-------------------+| File              | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+-------------------+----------+--------------+------------------+-------------------+| Master-bin. 000005 |      154 | | | |                   +-------------------+----------+--------------+------------------+-------------------+1 in Set (0.00 sec)

Theoretically, master-slave synchronization can begin to work properly. But the hole I dug for myself this time appeared ....

The slave server does not synchronize with the primary server:

View from server Status:

MySQL > show slave status\g

***************************1. Row ***************************Slave_io_state:master_host:192.168.1.71master_user:mysql_slave Master_port:3306Connect_retry: -Master_log_file:master-bin.000007Read_master_log_pos:856Relay_log_file:relay-log.000009Relay_log_pos:4Relay_master_log_file:master-bin.000007Slave_io_running:no Slave_sql_running:yes Replicate_do_db:replicate_ Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_I Gnore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:856Relay_log_space:154until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify _server_cert:no Last_io_errno:1593last_io_error:fatal error:the slave I/o thread stops because master and slave have equal MySQL server UUIDs; These uuids must be different forreplication to work. Last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1master_uuid:8f0581c9-695b-11e8-951e-000c295cdfc6 master_info_file:/var/lib/mysql/master.InfoSql_delay:0Sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log; waiting for  MoreUpdates Master_retry_count:86400Master_bind:last_io_error_timestamp:180607  One:Geneva: AboutLast_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid _set:executed_gtid_set:auto_position:0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1RowinchSet (0.00Sec

Slave_io_running:no

Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work.

The IO process from the server is not running, the error causes the master server to use the same UUIDs
This is the pit that was previously copied to stay. When I copied all the database files, the original generated UUIDs also copied. So there's this problem.
Not difficult to handle, UUIDs stored in MySQL data directory auto.cnf file
I directly edit the files from the server
Vim/var/lib/mysql/auto.cnf

[Auto]server-uuid=8f0581c9-695b-11e8-951e-000c295cdfc9# A random change of number on the line .... 16 Binary,

Then restart the MySQL service from the server

Re-viewing from server state

MySQL > show slave status\g

Slave_io_running:yes

Slave_sql_running:yes

last_io_errno:0

Last_io_error:

Master-Slave synchronization has started to work.

MySQL Master-Slave synchronization