Demo with two new MySQL servers: mysql1:10.10.1.55 (Master) mysql2:10.10.1.56 (slave) to configure the master and subordinate, the steps are as follows: step 1: Create a copy account on MYSQL1 replcreate USER ' Repl ' @ ' 10.10.1.% ' identified by ' Myrep8%go '; GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' repl ' @ ' 10.10.1.% '; step 2: Edit Mysql1 file on/etc/my.cnf, add two rows Log_b in=mysql-binserver_id=55 Open the binary log and set server_id (only the number, with the IP address after two bits), and then restart mysqld, log in with the root account, execute "SHOW MASTER STATUS statement, check that the output is consistent with the following mysql> SHOW MASTER status;+------------------+----------+--------------+----------------- -+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec) STEP 3: Edit the/etc/my.cnf file on the MYSQL2, add the following configuration, and then restart the mysqldlog_bin=mysql-binserver_id=56relay_log=/var/lib/mysql/ Mysql-relay-binlog_slave_updates=1read_only=1 if the master-master structure is used, the read_only is optionalConfigure step 4: Log in to the MYSQL2 database, execute the following statement, let Mysql2 connect to MYSQL1, and replay the binary log change MASTER to master_host= ' 10.10.1.55 ', master_user= ' Repl ', master_password= ' Myrep8%go ', master_log_file= ' mysqlbin.000001 ', master_log_pos=154; After successful execution, enter "SHOW SLAVE Status\g "Check that the configuration is correct as shown below mysql> show SLAVE status\g*************************** 1. Row ***************************slave_io_state:master_host:10.10.1.55master_user:replmaster_port:3306connect_ Retry:60master_log_file:mysql-bin.000001read_master_log_pos:4relay_log_file:mysql-relay-bin.000001relay_log_ Pos:4relay_master_log_file:mysql-bin.000001slave_io_running:noslave_sql_running:no slave_io_state is empty, Slave Both _io_running and slave_sql_running are no, indicating that replication is not running, and that you need to run the command "Start Slave" mysql> start Slave to initiate replication; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW SLAVE status\g*************************** 1. Row ***************************slave_io_state:waiting for Master to send Eventmaster_host:10.10.1.55master_user: Replmaster_port:3306connect_retry:60master_Log_file:mysql-bin.000001read_master_log_pos:623relay_log_file:mysql-relay-bin.000001relay_log_pos:836relay_ Master_log_file:mysql-bin.000001slave_io_running:yesslave_sql_running:yes PostScript: If the MYSQL1 is done again and mysql2 a similar configuration, The MYSQL1 can be set to MYSQL2 from the library, realize the "Master-master" hot standby structure. Typically, a highly available read-write detached database schema requires at least three servers: one master, one hot standby, and one read-only. Delete Master and slave configuration commands reset master: Delete all binary logs and recreate a new binary log reset slave All: Removes the location information from the master-slave replication relationship from slave and completely cleans up the replication connection parameter information mysql Restart the words to replay the binary log see if binary logs are turned on w variables like '%log_bin% '; Change MASTER to master_host= ' 192.168.237.101 ', master_user= ' repl ', master_password= ' root123! ', master_log_file= ' Mysqlbin.000004 ', Master_log_pos=0; master host unexpectedly stops running, reboot master needs to re-point to Master's binaries and Poschange master to Master_host = ' 192.168.237.101 ', master_user= ' repl ', master_password= ' root123! ', master_log_file= ' mysql-bin.000007 ', Master_log _pos=154;
CentOS 7 under MySql5.7 master-slave configuration
