I. Environmental preparation
This time, two Linux hosts, operating system for CentOS 6.8, have installed the same version of MySQL. (MySQL 5.7).
The firewalls of both master and slave servers are opened on port 3306.
The relevant information is as follows:
[Main Server]
IP: 192.168.236.100
[slave server]
IP: 192.168.236.101
II. Configuring master-slave replication
Principle of master-slave replication: MySQL is master-slave replication, mainly relying on binary logs. What the master server does, the slave server does.
The main server has a log function, which records all the additions, deletions and modifications it has done in the log. Get this log from the server and do it by yourself according to the action of the log. This implements master-slave replication.
1. First, modify the configuration / etc/my.cnf to support binary logging
[Main Server]
Add the following three lines of code to the configuration file:
100 in server-id is the last bit of the host IP, which is easy to distinguish. Of course, other values can be set, but not the same as slave servers.log-bin=mysql-bin binlog_format=mixed server-id=100
Save after adding and restart MySQL.
Parametric interpretation:
log-bin=mysql-bin // / Name mysql-bin binary log
There are three formats for binlog_format=mixed // binary logs: statement/row/mixed, with little explanation. Set mixed here.
Ser-id = 100 *// Setting up an independent ID for the server is easy to distinguish. Here, the last IP address is used as the server-id.
[slave server]
Adding code from the server according to the above operation is only different from the server-id.
log-bin=mysql-bin binlog_format=mixed server-id=101
Save the settings after completion and restart MySQL.
2. Assigning an account to the slave server on the master server is like a key. Only when the slave server holds the key, can the log files of the master server be shared on the master server.
[Main Server]
- Log in to the MySQL database of the primary server using root user and execute the following commands to create an account.
GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '1234';
- View the BIN log information of the primary server (record the values of File and Position after execution, and then do nothing before configuring the slave server, because these two values change every time you operate the server)
3. Setting up slave server
[slave server]
Log in to MySQL from the server using root user.
- Close slave (if master-slave replication has been set up before)
stop slave;
- Execute the following code
CHANGE MASTER TO MASTER_HOST="192.168.236.100",MASTER_USER="slave",MASTER_PASSWORD="1234",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=438;
MASTER_HOST: Set the IP address of the primary server to connect to
MASTER_USER: Set the username of the primary server to connect to
MASTER_PASSWORD: Set the password of the primary server to connect to
MASTER_LOG_FILE: Set the log name of the BIN log of the primary server to connect to, that is, the information obtained in step 2
MASTER_LOG_POS: Set the location of the BIN log to connect to the primary server, which is the information obtained in step 2. Note: Do not quote, otherwise the configuration will fail.
- Start the slave server
start slave;
- Look at the status of the slave server. If the values of Slave_IO_Running and Slave_SQL_Running are YES, the configuration is successful. If unsuccessful, repeat the above steps.
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 for master to send event | 192.168.236.100 | slave | 3306 | 60 | mysql-bin.000001 | 438 | CentOS6-relay-bin.000002 | 320 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 438 | 529 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 100 | 087d3459-2c02-11e7-a39a-000c29073d16 | /usr/local/mysql/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | | +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ 1 row in set (0.01 sec)
4. At this point, master-slave replication configuration is complete!
III. Testing
1. First look at the databases on the master and slave servers
[Main Server]
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
[slave server]
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
2. Create a new database monkey on the main server
create database monkey;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | monkey | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
3. Check whether there is monkey database on the slave server. If there is monkey database, the master-slave replication is normal. Be accomplished!
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | monkey | | mysql | | performance_schema | | sys | +--------------------+