MySQL and MariaDB traditional master-slave cluster configuration

MySQL and MariaDB traditional master-slave cluster configuration

The most basic cluster mode for MySQL and MariaDB is the master-slave mode, which is also the most widely used cluster mode for many small and medium-sized enterprises. This cluster is easy to deploy and can be used. Let's step by step.

Install MariaDB 10.1 source code in the default path/usr/local/mysql.

$ BUILD/autorun. sh

$./Configure

$ Make-j4

$ Sudo make install

As a master-slave database, at least two database instances are required. In this example, the two instances are deployed on the same computer and the following table is planned to be configured.

Instance role	Address	Port	Data File Path	Configuration File Path
Master	Localhost	10001	/Home/lyw/db/data/1	/Home/lyw/db/etc/my1.cnf
Slave	Localhost	10002	/Home/lyw/db/data/2	/Home/lyw/db/etc/my2.cnf

Prepare two mysql configuration files, my1.cnf. We copied them from the default configuration.

$ Mkdir-p/home/lyw/db/etc/

$ Cd/usr/local/mysql

$ Cp support-files/my-medium.cnf/home/lyw/db/etc/my1.cnf

$ Cp support-files/my-medium.cnf/home/lyw/db/etc/my2.cnf

Modify the following lines for my1.cnf:

[Mysqld]

Port = 10001

Socket =/tmp/mysql1.sock

Log-bin = mysql-bin

Servers-id = 10001

Datadir =/home/lyw/db/data/1

User = lyw

Modify the following lines for my2.cnf:

[Mysqld]

Port = 10002

Socket =/tmp/mysql2.sock

Log-bin = mysql-bin

Servers-id = 10002

Datadir =/home/lyw/db/data/2

User = lyw

Initialize two databases respectively

$ Mkdir-p/home/lyw/db/data/

$ Scripts/mysql_install_db -- defaults-file =/home/lyw/db/etc/my1.cnf

$ Scripts/mysql_install_db -- defaults-file =/home/lyw/db/etc/my2.cnf

Start two databases

Because it is in a computer, you do not use the service to start, but directly use mysqld_safe to start. You can start it as needed.

$ Bin/mysqld_safe -- defaults-file =/home/lyw/db/etc/my1.cnf &

$ Bin/mysqld_safe -- defaults-file =/home/lyw/db/etc/my2.cnf &

Check whether the two databases are successfully started. The corresponding port and sock file already exist, indicating that the database is successfully started.

$ Sudo netstat-nlp | grep mysql

Tcp6 0 0: 10001: * LISTEN 22211/mysqld

Tcp6 0 0: 10002: * LISTEN 23536/mysqld

Unix 2 [ACC] stream listening 365439 22211/mysqld/tmp/mysql1.sock

Unix 2 [ACC] stream listening 368511 23536/mysqld/tmp/mysql2.sock

By default, MariaDB can log on to any local user on two lines, which affects the login of newly created copy users. Therefore, you need to delete this line. Then create a copy user to take effect. (If you are running on two different computers, you can not delete these two lines .)

$ Bin/mysql-S/tmp/mysql1.sock-uroot

MariaDB [(none)]> use mysql;

MariaDB [mysql]> delete from user where user = '';

MariaDB [mysql]> flush privileges;

MariaDB [mysql]> grant replication slave on *. * to 'rep '@' % 'identified by '123 ';

Check the running status of the master database and write down the two values.

MariaDB [mysql]> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000005 | 1075 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

Run

$ Bin/mysql-S/tmp/mysql2.sock-uroot

MariaDB [(none)]> change master

Master_host = 'localhost ',

Master_port = 10001,

Master_user = 'rep ',

Master_password = '000000 ',

Master_log_file = 'mysql-bin.000005 ',

Master_log_pos = 1075;

 

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status \ G;

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: rep

Master_Port: 10001

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 1075

Relay_Log_File: lyw-hp-relay-bin.000002

Relay_Log_Pos: 537

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If both Slave_IO_Running and Slave_ SQL _Running are Yes, the master-slave configuration is successfully started.

Test:

Run

MariaDB [mysql]> create database lyw;

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Lyw |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

5 rows in set (0.00 sec)

Run the following command in the slave database:

MariaDB [(none)]> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Lyw |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

5 rows in set (0.00 sec)

It can be seen that the database lyw created in the master database is also available in the slave database. You can try to execute other write statements in the master database and view the same data in the cluster database.

In the same way, a primary database can be followed by multiple slave databases to increase data reliability and read throughput.

If the master database is also configured to synchronize data from the slave database, that is, the master and slave databases are mutually active, that is, the master database is usually called dual-master, or the master database, some articles will talk about modifying auto-increment IDs to prevent conflicts, but I do not think that the two databases of the dual master database are not strongly consistent, but have a certain delay, many transactions may encounter errors due to delay. Dual-master does not greatly increase the write throughput because all data must be written to each database. Although there can be a small increase due to batch, it is insignificant for errors caused by latency.

It makes sense to configure dual-master, but it is not used to write data at the same time. At the same time, only one database is written. When the master fails, it is prepared to switch the master and slave. After switching, when the master node is started again, the slave data can be synchronized immediately. For example, you can use dual-master configuration in a cobar cluster.

The above is a traditional master-slave replication method. From Mysql5.6 and MariaDB10.0, a new master-slave replication method is available. Refer to this article:

Linux Tutorial: How to check the MariaDB server version

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server