MySQL master and slave replication setup (using Mysqld_muti)

Here I use two MySQL instances on a single server to build, mainly uses the MySQL own Mysqld_multi

First, the principle of replication

A basic knowledge of the MySQL replication principle needs to be added before starting the build:

MySQL uses three threads for primary and standby replication:

1. On-the-go storage threads on the main library:

Writes the transaction committed by MySQL server to a binary file, which is called Binlog.

2. Connection threads on the standby library:

When the standby is started, it is responsible for communicating with the main library, reading the Binlog, and storing binlog into one of its own relaylog called the relay log.

3. Relaylog replay threads on the standby library:

This thread will play back the events in Relaylog on the standby, and the white point is to re-execute the

Second, the construction steps

1./etc New 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)

Three new instances in 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 mysqld3307 this instance as the Master library, mysqld3308 and mysql3309 as a repository

2. After the configuration is done, open two instances:

Mysqld_multi--defaults-file=/etc/mysql/mysqld_muti.cnf Start

3. Start the preparation of the main library before copying:

1. Add a copy account to the main library:

Log in to MySQL using the sock file:

Mysql-uroot-p-s/var/lib/mysql3307/mysql3307.sock (This socket file remember, is defined in the previous definition of the instance)

Mysql>grant replication Slave,replication Client on * * to [e-mail protected]' localhost ' identified by ' re Plication ';

Mysql>flush privileges;

To see if the Binlog on the main library is turned on:

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:

Log in to the standby repository:

Mysql-uroot-p-s/var/lib/mysql3308/mysql3308.sock

1. Change Master to Master_host = "localhost",

2. Master_user = ' Replication ',

3. Master_password = ' Replication ', (specified when you previously created the copy account on the main library)

4. Master_port = 3306;

Start slave;

To see if replication is starting to work:

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 seen Slave_io_running:yes and Slave_sql_running:yes prove that both the IO communication thread and the SQL playback thread have been started. At this point, the primary and standby replication fabric configuration is complete

5. Perform the 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 log status:

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 time, the main library mysql3307 the flush logs operation, regenerated the Mysql-bin log, and added the table to the delete operation. Then start the copy from the library to 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 ' [email protected]:3307 ', replication started in log ' Mysql-bi N.000005 ' at position 408

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

Note: All data from the stop period is re-added from the library at the point in time.

6. Perform exception testing:

Stop replication from libraries mysql3308 and mysql3309, and log cleanup for the main library:

Flush logs

Delete from Sky where id= ' 2000 ';

Purge binary logs to ' mysql-bin.000010 ';

Then restart the copy from the library, log 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 Fir St log file name in binary log index file ', error_code:1236

Workaround:

can only stop from the database, and then re-change from the database to the MASTER to start , point 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.

This article is from the "Autumn Fairy tale" blog, please be sure to keep this source http://wushank.blog.51cto.com/3489095/1649929

MySQL master and slave replication setup (using Mysqld_muti)