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