Simple replication and application extension of Mariadb/mysql

Database replication in the actual production environment for improving the security of data and database performance is an important means, but no matter how clever, you should start from a simple copy! Today's experiment begins with a simple copy!

Experiment Preparation

Server Type/Name	IP Address	Software version	Platform
Master/node4	192.168.1.114	mariadb-10.0.10	CentOS 6.5 x86_64
Slave/node3	192.168.1.113	mariadb-10.0.10	CentOS 6.5 x86_64

Note: Software generally has up-to-date compatibility, so the software version of the master node should be lower or the same as from the node, and not higher than the slave node.

In addition, NODE3, NODE4 has been deployed MARIADB, the deployment process is no longer introduced here, want to know the friend can poke here

First, mariadb simple master-slave replication

1. Configuring the NTP server

Why would you want to configure an NTP server? Because data master-slave synchronization is mainly based on the primary node of the binary log, master-slave node if the time is not synchronized, synchronization data will be a big problem.

The NTP service is already the default service on CentOS 6.5, simple to start the service, but in the actual production environment, according to the actual need to configure. Here, we start the NTP service on Node1 (Internet connection available)

[[Email protected] ~]# service ntpd startstarting ntpd:                                             [  OK  ] Sync time [[email protected] ~]# ntpdate 192.168.1.111[[email protected] ~]# ntpdate 192.168.1.111

2. Configuring the Master node (NODE4)

For the master node we have to make few changes, because the test environment is simple, mainly in the/etc/my.cnf to do three changes:

(1) Modify the Server-id, in the master-slave structure, not a node of the Server-id should be unique;

(2) Modify the storage path of the binary log, in the application of mariadb, it is very important that the binary log and database data can not be stored in the same directory (preferably not on the same disk), 650) this.width=650; "title=" image 05111629. JPG "alt=" wkiom1nvngxrrsxoaael6ilhgxo563.jpg "src=" http://s3.51cto.com/wyfs02/M01/26/EE/ Wkiom1nvngxrrsxoaael6ilhgxo563.jpg "/>

(3) Create a user account with copy permission;

MariaDB [(None)]> grant replication client,replication slave on * * to ' slave ' @ ' 192.168.1.% ' identified by ' slavepass ';

3. Configure slave nodes (NODE3)

Make the following changes in/ETC/MY.CNF:

(1) Modify the Server-id, and there is no special specification, and the main node is different;

Server-id       = 200

(2) Start the relay log (if the node is not secondary from node, that is, the end node when the binary log is closed);

1, close the binary log only need to log off the binary log line, the other way in MariaDB-10.0.10 is not off; 2. Turn on the relay log Relay_log=/data/relaylogs/relay-log

(3) Connect to the master node and start synchronizing;

MariaDB [(none)]> change master to master_host= ' 172.16.26.4 ', master_user= ' slave ', master_password= ' slavepass '; MariaDB [(None)]> start slave;

At this point, the simple master-slave has been set up, and has synchronized good data, is it simpler than the imagination? Look at the results from the node (picture is not the first sync result): 650) this.width=650; "title=" Image 05111650.jpg "alt=" Wkiol1nvoumgvlyhaajesdrmjly411.jpg "src= "Http://s3.51cto.com/wyfs02/M01/26/EF/wKioL1NvOumgVLYHAAJeSDrMJLY411.jpg"/>

Second, the application of replication extension

1, How to restrict the only read operation from the node?

It is important for data security to read from a node only. It's very tall, but it's simple to implement, just add it in the/etc/my.cnf.

Read-only = On

Then restart Mariadb to view the status from node: 650) this.width=650; "title=" Image 05111725.jpg "alt=" wkiom1nvqzqyoo8faafqoyckq2i882.jpg "src=" Http://s3.51cto.com/wyfs02/M01/26/EF/wKiom1NvQzqyoo8FAAFQoYckq2I882.jpg "/>

Obviously the read-only state has been activated.

Note: This action only shows those users who do not have super privileges.

2, How to do the master-slave replication transaction security?

In the default mechanism of MARIADB, in order to improve the IO performance of the binary log, the transaction submitted by the user is not written directly into the binary log, but is cached in the binary log buffer, but such a transaction is unsafe, because if the database crashes at this time, Then the transaction in the binary log buffer cannot be saved to the binary log and then synchronized to the slave node, and the data is rolled back when the data is restored.

To avoid this, we should let the transaction department cache, and write directly to the binary log, although this will reduce the performance of the database system, but performance-based data security is worth it! So we need to add the/ETC/MY.CNF

sync_binlog=n# #设定多久同步一次二进制日志至磁盘文件中, 0 means no synchronization, and any positive value indicates that the binary is synchronized once per n write operations. # #当autocommit的值为1时, the execution of each statement causes the binary log to synchronize, otherwise the commit of each transaction causes the binary log to synchronize.