I. MySQL master-slave replication configuration

In my previous article four • Install mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz (based on CENTOS7 source installation and nine. MySQL database multi-instance installation Mysqld_multi [Start,stop, Report] Two articles to the single-server multi-instance installation, this article is about the configuration of master-slave replication. This time the mysql3306 as the main master,mysql3307 and mysql3308 as the slave

One. Start 3 MySQL Servers

[Email protected] ~~]$ mysqld_multi reportreporting MySQL serversmysql server from group:mysqld3306 are Runn Ingmysql server from group:mysqld3307 are Runningmysql server from group:mysqld3308 are running

Two. Log in to master master server mysql3306

[Email protected] ~]$ mysql-u root-s/tmp/mysql3306.sock-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs3Server Version:5.7. the-log MySQL Community Server (GPL) Copyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>

Three. Set up an account on Master master server mysql3306 and authorize slave

 mysql> Grant Replication Slave on * * to  "  Mysqlsync   " @"   %   " identified by "  mysqlsync  "  ; Query OK,  0  rows affected, 1  Warning ( 0.00  sec) 
 
 

Mysql> Show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306_bin.000006 | 442 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

Four. Login slave from server mysql3307 or mysql3308

[Email protected] ~]$ mysql-u root-s/tmp/mysql3307.sock-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs5Server Version:5.7. the-log MySQL Community Server (GPL) Copyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>

Five. Configure slave from server mysql3307 or mysql3308

mysql> Change Master to master_host='127.0.0.1', master_user='Mysqlsync', master_password='Mysqlsync', master_log_file='mysql3306_bin.000006', master_log_pos=442; Query OK,0Rows affected,2Warnings (0.05sec) MySQL>start slave; Query OK,0Rows Affected (0.03sec) MySQL>

Six. View the slave status on slave from the server

Mysql> mysql>show slave status;+----------------------------------+-------------+-------------+-------------+---------------+----------------- -----+---------------------+-------------------------------+---------------+-----------------------+----------- -------+-------------------+-----------------+---------------------+--------------------+---------------------- --+-------------------------+-----------------------------+------------+------------+--------------+----------- ----------+-----------------+-----------------+----------------+---------------+--------------------+---------- ----------+--------------------+-----------------+-------------------+----------------+-----------------------+ -------------------------------+---------------+---------------+----------------+----------------+------------- ----------------+------------------+--------------------------------------+------------------------------------ ----+-----------+---------------------+--------------------------------------------------------+--------------- -----+-------------+-------------------------+--------------------------+----------------+--------------------+- -------------------+-------------------+---------------+----------------------+--------------+----------------- ---+| Slave_io_state | Master_host | Master_user | Master_port | Connect_retry | Master_log_file | Read_master_log_pos | Relay_log_file | Relay_log_pos | Relay_master_log_file | slave_io_running | slave_sql_running | replicate_do_db | replicate_ignore_db | replicate_do_table | replicate_ignore_table | replicate_wild_do_table | replicate_wild_ignore_table | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | until_condition | Until_log_file | Until_log_pos | master_ssl_allowed | Master_ssl_ca_file | Master_ssl_ca_path | Master_ssl_cert | Master_ssl_cipher | Master_ssl_key | Seconds_behind_master | Master_ssl_verify_server_cert | Last_io_errno | Last_io_error | Last_sql_errno | Last_sql_error | Replicate_ignore_server_ids | master_server_id | Master_uuid | Master_info_file | Sql_delay | Sql_remaining_delay | Slave_sql_running_state | Master_retry_count | Master_bind | Last_io_error_timestamp | Last_sql_error_timestamp | MASTER_SSL_CRL | Master_ssl_crlpath | Retrieved_gtid_set | Executed_gtid_set | auto_position | replicate_rewrite_db | Channel_name | Master_tls_version |+----------------------------------+-------------+-------------+-------------+------------- --+----------------------+---------------------+-------------------------------+---------------+--------------- --------+------------------+-------------------+-----------------+---------------------+--------------------+-- ----------------------+-------------------------+-----------------------------+------------+------------+------ --------+---------------------+-----------------+-----------------+----------------+---------------+----------- ---------+--------------------+--------------------+-----------------+-------------------+----------------+----------------- ------+-------------------------------+---------------+---------------+----------------+----------------+------ -----------------------+------------------+--------------------------------------+----------------------------- -----------+-----------+---------------------+--------------------------------------------------------+-------- ------------+-------------+-------------------------+--------------------------+----------------+-------------- ------+--------------------+-------------------+---------------+----------------------+--------------+--------- -----------+|  Waiting forMaster to send Event |127.0.0.1| Mysqlsync |3306| -| Mysql3306_bin.000006|442| Iz25ufmpy4sz-relay-bin.000002|324| Mysql3306_bin.000006| Yes |                 Yes |                     |                    |                        |                         |                             | |0| |0|442|538|                None | |0|                    No |                    |                 |                   |                | |0| No |0| |0|                             | |3306| 526196c9-dbe6-11e6-aa52-00163e0066e2 | /home/mysql/mysql3307/data/master.Info|0| NULL | Slave have read all relay log; Waiting for  MoreUpdates |86400|                         |                          |                |                    |                    |                   | |0 |              |                    | |+----------------------------------+-------------+-------------+-------------+---------------+---------------- ------+---------------------+-------------------------------+---------------+-----------------------+---------- --------+-------------------+-----------------+---------------------+--------------------+--------------------- ---+-------------------------+-----------------------------+------------+------------+--------------+---------- -----------+-----------------+-----------------+----------------+---------------+--------------------+--------- -----------+--------------------+-----------------+-------------------+----------------+----------------------- +-------------------------------+---------------+---------------+----------------+----------------+------------ -----------------+------------------+--------------------------------------+----------------------------------- -----+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+----- ---------------------+----------------+--------------------+--------------------+-------------------+---------- -----+----------------------+--------------+--------------------+1RowinchSet (0.00Sec

Seven. master-Slave Server configuration test

To create a new database on the Master master server, the slave should also be able to query from the server, as follows

---------------primary server [[email protected]~]$ mysql-u root-s/tmp/mysql3306.sock-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs theServer Version:5.7. the-log MySQL Community Server (GPL) Copyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>CREATE database test; Query OK,1Row affected (0.00sec) MySQL>

--------------from the server

[Email protected] ~]$ mysql-u root-s/tmp/mysql3307.sock-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 13
Server Version:5.7.15-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> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| SYS |
| Test |
+--------------------+
5 rows in Set (0.00 sec)

Mysql>

I. MySQL master-slave replication configuration