5 min To understand and deploy MARIADB (MySQL) master-slave replication

A copy overview

      MariaDB Built-in replication capabilities are the foundation for building large, high-performance applications. mariadb Slaves

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/88/60/wKioL1f0tROzIpxFAAB6DnoTB1A683.png "title=" t[ fgoi5r@) ya9c4$i~~5} ' 8.png "alt=" Wkiol1f0trozipxfaab6dnotb1a683.png "/>

1 MariaDBsupported replication types:

(1): statement-based replication:  performed on the primary server.SQLstatement to execute the same statement from the server. MariaDBby default, statement-based replication is relatively efficient.
once it is found that it cannot be accurately reproduced,   row-based replication is automatically selected.
(2): Row-based replication: Copy the changed content past, instead of executing the command on the server. frommysql5.0start supporting
(3): Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

2 Primary several ways to copy from

Synchronous replication

The so-called synchronous replication, meaning master changes, must wait for slave-1,slave-2,..., Slave-n to complete before returning.

This is obviously undesirable and is not the default setting for MySQL replication. For example, on a web front-end page, users add a record and wait a long time.

Asynchronous replication

Just like an AJAX request. Master only needs to complete its own database operation. As to whether the slaves received the binary log, whether to complete the operation, do not care. The default settings for MySQL.

Semi-synchronous replication

Master only one operation in the slaves is guaranteed to be successful, the other slave is returned, regardless.

This feature was introduced by Google for MySQL.

Two-deployment

1, the master-slave server for the following actions:
1.1, version consistent
1.2. Initialize the table and start the MARIDB in the background
1.3, change the root password

2. Modify master server Master:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must be] enable binary log
server-id=222//[must be] server unique ID, default is 1, usually take IP last paragraph

3. Modify the slave from the server:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[Do not have to enable binary logging
server-id=226//[must be] server unique ID, default is 1, usually take IP last paragraph

4. Restart MySQL for two servers
Systemctl Restart MARIADB

5. Create an account on the master server and GrantRight Slave:

Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ '% ' identifiedby ' q123456 '; //Generally do not use the root account,“%” means that all clients may even, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.145.226, enhance security.

6, log on to the master server MySQL, query the status of Master
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |             308|                 | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

7. Configure the slave from the server:
Mysql>change Master Tomaster_host= ' 192.168.145.222 ', master_user= ' Mysync ', master_password= ' q123456 ',
Master_log_file= ' mysql-bin.000004 ', master_log_pos=308; //Be careful not to disconnect, there are no single quotes around 308 digits.

Mysql>start slave; To start the Copy from Server feature

8. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting Formaster to send event
master_host:192.168.2.222//Primary server address
Master_user:mysync//Authorization account name, try to avoid using root
master_port:3306//Database port, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:600//#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos
relay_log_file:ddte-relay-bin.000003
relay_log_pos:251
relay_master_log_file:mysql-bin.000004
Slave_io_running:yes//This status must be Yes
Slave_sql_running:yes//This status must be Yes
......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).
650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/88/64/wKiom1f0tT-TSAtNAADQRK_zq6o089.png "title=" b7ive[ J~v081523xiymea4j.png "alt=" wkiom1f0tt-tsatnaadqrk_zq6o089.png "/> Above operation process, master and slave server configuration is complete.

9, the master-slave server test:

( This is the primary server)

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/88/64/wKiom1f0tWCT6rXrAACF3YLWgi8116.png "title=" OA0DFB %2tk43_q7~sxac@j7.png "alt=" Wkiom1f0twct6rxraacf3ylwgi8116.png "/>

Log in again from the server to view

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/88/60/wKioL1f0tXDjEwI-AAA0JbpAZKA692.png "title=" Z (~ udk8%1%39oypc0b67c ' 4.png "alt=" Wkiol1f0txdjewi-aaa0jbpazka692.png "/>

This indicates that the master-slave replication has been achieved;

This article is from the "Linux Little Boy" blog, please be sure to keep this source http://zjwliunx.blog.51cto.com/11010135/1858844

5 min To understand and deploy MARIADB (MySQL) master-slave replication