Configure MySQL master-slave replication in Linux

Configure MySQL master-slave replication in Linux

MySQL master-slave replication is a practical experience that you have always wanted to learn, because the master-slave replication in Oracle also requires a lot of configuration options. In contrast, it feels heavyweight (data volume is also heavyweight ).
I tried it today and simulated it on a local Linux virtual machine. I found it quite interesting. As a layman, I watched a very early video to quickly achieve the effect. As a result, several configuration parameters were missing during actual operations. It seems that we still need to calm down and do it slowly. We configured it on Windows for various reasons, that is, it was not configured. I still like it in Linux, today, I am adding a bit of strength.
There are several general steps,
Create two operating system users and add them to the dba group.
Install MySQL on the user of the master database
Install MySQL on the user of the slave Library
Configure master-slave replication relationships
Start slave check

First, let's look at the schematic diagram. It is estimated that this diagram is shared everywhere. We can see that the basic principle of master-slave replication is to use binlog. This binlog (binary log) can flexibly parse the corresponding SQL statement, basically doing a similar playback on the slave end. However, this process lasts for a short period of time, with a small amount of data and a small transaction volume.

 

Let's see how it works. If you do not want to use the default value for MySQL, you will not select the rpm package installation and yum installation methods to directly obtain the Lib package from the website. Directly decompress the package to run the task.
For the default port 3306, you can set it to 1550. The Log Path and other configurations are completed manually, which is necessary for MySQL processing in the background.
The installation process is very easy.
In the path of multiple decompressed files in glibc, I configured a variable of MYSQL_BASE and installed it by hand.
[Mysql @ oel1 mysql-advanced-5.6.23-linux-glibc2.5-i686] $ ll
Total 288
-Rw-r -- 1 mysql dba 120908 Apr 7 a. log
Drwxr-xr-x 2 mysql dba 4096 Apr 5 bin
Drwxr-xr-x 2 mysql dba 4096 Apr 5 docs
Drwxr-xr-x 3 mysql dba 4096 Apr 5 include
-Rw-r -- 1 mysql dba 102509 Jan 29 INSTALL-BINARY
Drwxr-xr-x 3 mysql dba 4096 Apr 5 lib
-Rw-r -- 1 mysql dba 2729 Jan 29 LICENSE. mysql
Drwxr-xr-x 4 mysql dba 4096 Apr 5 man
-Rw-r -- 1 mysql dba 1029 Apr 5 my. cnf
-Rw-r -- 1 mysql dba 1029 Apr 14 my-new.cnf
Drwxr-xr-x 10 mysql dba 4096 Apr 5 mysql-test
-Rw-r -- 1 mysql dba 1449 Jan 29 README
Drwxr-xr-x 2 mysql dba 4096 Apr 5 scripts
Drwxr-xr-x 28 mysql dba 4096 Apr 5 share
Drwxr-xr-x 4 mysql dba 4096 Apr 5 SQL-Audit
Drwxr-xr-x 2 mysql dba 4096 Apr 5 support-files
The configured profile variables are as follows:
PATH = $ PATH: $ HOME/bin
Export MYSQL_BASE =/u02/mysql/mysql-advanced-5.6.23-linux-glibc2.5-i686.
Export MYSQL_DATA =/u02/mysql/data
Export PATH = $ PATH: $ MYSQL_BASE/bin: $ MYSQL_DATA

Then run the command to install it, which is initialization. The specified port is 1550.
$ MYSQL_BASE/scripts/mysql_install_db -- user = mysql -- basedir = $ MYSQL_BASE -- datadir = $ MYSQL_DATA -- port = 1550

Then we can start mysql. There are more options to start the command. We don't apply the. cnf file to configure it. You can check it manually.
$ MYSQL_BASE/bin/mysqld_safe -- user = mysql -- basedir = $ MYSQL_BASE -- datadir = $ MYSQL_DATA -- port = 1550 -- log-bin = mysql_oel1_bin -- log-error =/u02/mysql/ log/log_error.log -- long_query_time = 5 -- slow-query-log = true -- slow-query-log-file =/u02/mysql/log/slow_qeury.log -- socket =/u02/mysql/ mysqld_mst.sock -- server-id = 1 &

This is done on the master end.
The work on the slave side is similar, or if you are lazy, you can directly copy the decompressed package. If you need to re-set the Port during installation and initialization, locate 1551.
Do not configure slave first to see if it can be started.
$ MYSQL_BASE/bin/mysqld_safe -- user = mysql -- basedir = $ MYSQL_BASE -- datadir = $ MYSQL_DATA -- port = 1551 -- log-bin = mysql_oel1_slv1_bin -- log-error =/u02/mysqlslv1/ log/log_error.log -- long_query_time = 5 -- slow-query-log = true -- slow-query-log-file =/u02/mysqlslv1/log/slow_qeury.log -- socket =/u02/mysqlslv1/ mysql_slv1.sock

After it is started, the verification is correct and we can start the configuration.
The master-slave configuration is relatively simple. If too many rules are required, it is easy.
In the master database, we need to configure a user for replication.
Mysql> grant replication slave on *. * to 'slaveuser' @ '192. 168.27.130 'identified by 'slaveuser ';
Query OK, 0 rows affected (0.00 sec)
This new user is reflected in the user.
Mysql> select host, user, password from user;
+ ---------------- + ----------- + ------------------------------------------- +
| Host | user | password |
+ ---------------- + ----------- + ------------------------------------------- +
| Localhost | root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| Oel1 | root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 127.0.0.1 | root |
|: 1 | root |
| % | Test |
| 192.168.27.130 | slaveuser | * 7890E850D4C4DF85267DEC80307D6CCAEC115B2F |
+ ---------------- + ----------- + ------------------------------------------- +
6 rows in set (0.00 sec)

View master Status
Mysql> show master status;
+ ----------------------- + ---------- + -------------- + ------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ----------------------- + ---------- + -------------- + ------------------ + ------------------- +
| Mysql_oel1_bin.000004 | 520 |
+ ----------------------- + ---------- + -------------- + ------------------ + ------------------- +
1 row in set (0.00 sec)

Then go to the slave end and restart it. Set server-id to 2 and specify port 1551.
$ MYSQL_BASE/bin/mysqld_safe -- user = mysql -- basedir = $ MYSQL_BASE -- datadir = $ MYSQL_DATA -- port = 1551 -- log-bin = mysql_oel1_slv1_bin -- log-error =/u02/mysqlslv1/ log/log_error.log -- long_query_time = 5 -- slow-query-log = true -- slow-query-log-file =/u02/mysqlslv1/log/slow_qeury.log -- socket =/u02/mysqlslv1/ mysql_slv1.sock -- server-id = 2 &

After the Server Load balancer is started, run the following statement to configure the Server Load balancer instance. This process is like a db link.
Mysql> change master to master_host = '192. 168.27.130 ', master_user = 'slaveuser', master_password = 'slaveuser', master_port = 192;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
After startup. You can directly view the show slave status \ G to see a basic situation on the slave side. The operation may cause some replication problems. You can specify the skip range. perform the following operations.

Mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

Mysql> set global SQL _slave_skip_counter = 100;
Query OK, 0 rows affected (0.02 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.27.130
Master_User: slaveuser
Master_Port: 1550
Connect_Retry: 60
Master_Log_File: mysql_oel1_bin.000005
Read_Master_Log_Pos: 436
Relay_Log_File: oel1-relay-bin.000010
Relay_Log_Pos: 288
Relay_Master_Log_File: mysql_oel1_bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 98
Exec_Master_Log_Pos: 436
Relay_Log_Space: 628
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 02c8b5e4-e295-11e4-8db2-000c296415de
Master_Info_File:/u02/mysqlslv1/data/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_ SQL _Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

Then we delete a new table on the master end, which can be invisible from the slave end.
Tables in slave before creation
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Innodb_test |
| Myisam_test |
| New |
| New_test |
| Test3 |
| Test4 |
+ ---------------- +
6 rows in set (0.00 sec)

After the creation, view it immediately and synchronize it soon.
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Innodb_test |
| Myisam_test |
| New_test |
| Test3 |
| Test4 |
+ ---------------- +
5 rows in set (0.00 sec)

And so on, it was easy to create another slave environment in over 10 minutes. The overall feeling is light and easy.

-------------------------------------- Split line --------------------------------------

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: