MySQL master-slave replication (using mysqld_multi)

MySQL master-slave replication (using mysqld_multi)

Here, I use two MySQL instances on a single server to build them, mainly using the mysqld_multi
 
I. Replication Principle
Before starting the setup, you need to add the following basic knowledge about mysql replication principles:
Mysql uses three threads for master-slave replication:
1. The storage transfer thread on the master database:
The transaction committed by the mysql server is written into the binary file, which is called binlog.
2. Connection threads on the slave database:
After the slave database starts, it is responsible for communicating with the master database, reading the binlog, and storing the binlog in its own relaylog called a relay log.
3. relaylog replay thread on the slave database:
This thread will play back the events in the relaylog on the slave database. In other words, the white point is a re-execution.
 
Ii. Setup steps
1. Add the mysqld_multi.cnf file to/etc.
Copy the/user/share/mysql/my-innodb-heavy-4G.cnf file to/etc and rename it xxx. cnf (any name you want)
Add three instances to the configuration file.

--------------------------------------------------------------------------------
[Mysqld_multi]
Mysqld =/usr/bin/mysqld_safe
Mysqladmin =/usr/bin/mysqladmin
User = multi_admin
Password = multipass
Log =/var/log/mysqld_multi.log

[Mysqld3307]
Port = 3307
Pid-file =/var/lib/mysql3307/mysql3307.pid
Socket =/var/lib/mysql3307/mysql3307.sock
Datadir =/var/lib/mysql3307
User = mysql
Set-variable = max_connections = 27000
Log_bin = mysql-bin
Server_id = 3307

[Mysqld3308]
Port = 3308
Pid-file =/var/lib/mysql3308/mysql3308.pid
Socket =/var/lib/mysql3308/mysql3308.sock
Datadir =/var/lib/mysql3308
User = mysql
Set-variable = max_connections = 28000
Log_bin = mysql-bin
Server_id = 3308
Relay_log =/var/lib/mysql3308/mysql-relay-bin
Log_slave_updates = 1
Read_only = 1

[Mysqld3309]
Port = 3309
Pid-file =/var/lib/mysql3309/mysql3309.pid
Socket =/var/lib/mysql3309/mysql3309.sock
Datadir =/var/lib/mysql3309
User = mysql
Set-variable = max_connections = 29000
Log_bin = mysql-bin
Server_id = 3309
Relay_log =/var/lib/mysql3309/mysql-relay-bin
Log_slave_updates = 1
Read_only = 1

--------------------------------------------------------------------------------

Here, I will use mysqld3307 as the master database, and mysqld3308 and mysql3309 as the slave database.
 
 
2. enable two instances after Configuration:
Mysqld_multi -- defaults-file =/etc/mysql/mysqld_muti.cnf start

 
3. start preparations for the master database before replication:
1. Add a copy account to the master database:
Log on to mysql using the sock file:
Mysql-uroot-p-S/var/lib/mysql3307/mysql3307.sock)

--------------------------------------------------------------------------------
Mysql> grant replication slave, replication client on *. * to replication @ 'localhost' identified by 'replicase ';

Mysql> flush privileges;

--------------------------------------------------------------------------------
Check whether binlog on the master database is Enabled:

--------------------------------------------------------------------------------

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 1001 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

--------------------------------------------------------------------------------
 
4. enable replication:
Log on to the slave database:
Mysql-uroot-p-S/var/lib/mysql3308/mysql3308.sock
 

--------------------------------------------------------------------------------

Change master to master_host = "localhost ",

Master_user = 'replicase ',

Master_password = 'replicase' (which you specified when creating a replication account on the master database)

Master_port = 3306;

 
Start slave;

 
Check whether the replication process starts:

--------------------------------------------------------------------------------
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: replication
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 622
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 767
Relay_Master_Log_File: mysql-bin.000001
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: 0
Exec_Master_Log_Pos: 622
Relay_Log_Space: 922
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:
1 row in set (0.00 sec)

--------------------------------------------------------------------------------
When we see Slave_IO_Running: Yes and Slave_ SQL _Running: Yes, both the io communication thread and the SQL playback thread have been started. Now, the master-slave replication structure is configured

5. Perform a normal master-slave test:
Stop replication in the mysql3308 Database

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

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: replication
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 408
Relay_Log_File: mysql-relay-bin.000012
Relay_Log_Pos: 553
Relay_Master_Log_File: mysql-bin.000005

--------------------------------------------------------------------------------

Stop replication in the mysql3309 Database

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

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: replication
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 316

--------------------------------------------------------------------------------

View mysql logs:

--------------------------------------------------------------------------------
150510 1:33:39 [Note] Error reading relay log event: slave SQL thread was killed
150510 1:33:39 [Note] Slave I/O thread killed while reading event
150510 1:33:39 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000005 ', position 408

150510 1:35:41 [Note] Error reading relay log event: slave SQL thread was killed
150510 1:35:41 [Note] Slave I/O thread killed while reading event
150510 1:35:41 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000006 ', position 316

--------------------------------------------------------------------------------

During this period, the master database mysql3307 performed the flush logs operation, re-generated the mysql-bin log, and added and deleted the table. Then, start the slave database replication and view it.

--------------------------------------------------------------------------------

150510 1:42:48 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005 'at position 408, relay log'/var/lib/mysql3308/mysql-relay-bin.000012' position: 553
150510 1:42:48 [Note] Slave I/O thread: connected to master 'replication @ localhost: 100', replication started in log 'mysql-bin.000005 'at position 3307

150510 1:43:04 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006 'at position 316, relay log'/var/lib/mysql3309/mysql-relay-bin.000015' position: 461
150510 1:43:04 [Note] Slave I/O thread: connected to master 'replication @ localhost: 100', replication started in log 'mysql-bin.000006 'at position 3307

--------------------------------------------------------------------------------
Note: The slave database re-complements all data during the stop period from the stop time point.

6. perform an exception test:
Stop the replication of the slave database mysql3308 and mysql3309, and clear the logs of the master database:

--------------------------------------------------------------------------------
Flush logs
Delete from sky where id = '123 ';
Purge binary logs to 'mysql-bin.000010 ';

--------------------------------------------------------------------------------
Then restart slave database replication, log errors, data loss, Master/Slave failure.

--------------------------------------------------------------------------------
150510 1:50:53 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236

--------------------------------------------------------------------------------
Solution:
You can only stop the slave database, and then change master to from the database again, pointing TO the correct binary file and offset

--------------------------------------------------------------------------------
Change master to master_log_file = 'mysql-bin.000001 ', master_log_pos = 106;

--------------------------------------------------------------------------------
The subsequent data will resume normal master-slave synchronization.

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

How to install and configure MySQL5.7.3.0

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

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

This article permanently updates the link address: