| |
Here I use a single server on the two examples of MySQL structures, the main use of the MySQL mysqld_multi
First, the replication principle
Began to build a mysql replication before the basic knowledge of the need to add:
Mysql for the use of the main preparation and replication to the three threads:
1. The main database on the transfer storage thread:
Mysql server will be submitted to the affairs of the document into the binary file, the binaries called binlog.
2. The connection thread on the standby library:
After the standby library is started, it is responsible for communicating with the main library and reading binlog. At the same time, binlog is stored in one of its relaylogs called relaylog.
3. Backuplog on the backup library Thread:
This thread will be relaylog events in the standby library on the playback, said the white point is to re-run once
Second, build the steps
1./etc Add the file mysqld_multi.cnf
Copy the /user/share/mysql/my-innodb-heavy-4G.cnf file to / etc and rename it to 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 be mysqld3307 this instance as the main library, mysqld3308 and mysql3309 this instance as a backup library
2. After doing the configuration, open two instances:
Mysqld_multi --defaults-file = / etc / mysql / mysqld_muti.cnf start
3. Prepare the main library before copying:
1. In the main library to add a copy account:
Use the sock file login mysql:
Mysql-uroot-p -S / var / lib / mysql3307 / mysql3307.sock (the socket file remember, it is defined before the definition of the instance)
-------------------------------------------------- ------------------------------
Mysql> grant replication slave, replication client on *. * To replication @ 'localhost' identified by 'replication';
Mysql> flush privileges;
-------------------------------------------------- ------------------------------
View the main library on the binlog is open:
-------------------------------------------------- ------------------------------
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ---- -------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ---- -------------- +
| Mysql-bin.000001 | 1001 |
+ ------------------ + ---------- + -------------- + ---- -------------- +
1 row in set (0.00 sec)
-------------------------------------------------- ------------------------------
4. Turn on replication:
Login to the standby:
Mysql -uroot -p -S /var/lib/mysql3308/mysql3308.sock
-------------------------------------------------- ------------------------------
Change master to master_host = "localhost",
Master_user = 'replication',
Master_password = 'replication', (you previously created in the main library copy account specified)
Master_port = 3306;
Start slave
To see if replication is working:
-------------------------------------------------- ------------------------------
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 you see Slave_IO_Running: Yes and Slave_SQL_Running: Yes verify that both the io communication thread and the sql playback thread are already started. At this point, the active-standby replication fabric is configured
5. Perform normal master-slave test:
The mysql3308 database stops replication
-------------------------------------------------- ------------------------------
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
-------------------------------------------------- ------------------------------
The mysql3309 database stops replication
-------------------------------------------------- ------------------------------
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 log situation:
-------------------------------------------------- ------------------------------
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 main library mysql3307 flush logs operation, re-generated mysql-bin log, and the table to add, delete operations. Then start the copy from the library to view.
-------------------------------------------------- ------------------------------
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: 3307', replication started in log 'mysql-bin.000005' at position 408
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: 3307', replication started in log 'mysql-bin.000006' at position 316
-------------------------------------------------- ------------------------------
Explanation: All data for the stop period have been replenished from the point in time when the library was stopped.
6. Abnormal test:
Stop from the library mysql3308 and mysql3309 copy, and the main library for log removal:
-------------------------------------------------- ------------------------------
Flush logs
Delete from sky where id = '2000';
Purge binary logs to 'mysql-bin.000010';
-------------------------------------------------- ------------------------------
And then restart the replication from the library, the log for error, 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:
Can only be stopped from the database and then restarted at the beginning of the CHANGE MASTER TO from the database, pointing to the correct binaries and offsets
-------------------------------------------------- ------------------------------
Change master to master_log_file = 'mysql-bin.000001', master_log_pos = 106;
-------------------------------------------------- ------------------------------
After the data will be restored to normal master-slave synchronization.
|
|
|
|