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. The version used for the test is MariaDB10.1 source code installation, installed under the default path usrlocalmysql. $ BUILDautorun. sh $. configure $ make-j4 $ sudo
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
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 = 10001socket = /tmp/mysql1.socklog-bin = mysql-binserver-id = 10001datadir = /home/lyw/db/data/1user = lyw
Modify the following lines for my2.cnf:
[mysqld]port = 10002socket = /tmp/mysql2.socklog-bin = mysql-binserver-id = 10002datadir = /home/lyw/db/data/2user = 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 mysqltcp6 0 0 :::10001 :::* LISTEN 22211/mysqld tcp6 0 0 :::10002 :::* LISTEN 23536/mysqld unix 2 [ ACC ] STREAM LISTENING 365439 22211/mysqld /tmp/mysql1.sockunix 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 -urootMariaDB [(none)]> use mysql;MariaDB [mysql]> delete from user where user='';MariaDB [mysql]> flush privileges;MariaDB [mysql]> grant replication slave on *.* to 'rep'@'%' identified by '123456';
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 -urootMariaDB [(none)]> change master to master_host='localhost', master_port=10001, master_user='rep', master_password='123456', 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 configured to synchronize data from the slave database, that is, the master and slave databases are mutually usedDual-Master, OrMasterSome 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 the traditional master-slave replication method, from Mysql5.6 and MariaDB10.0, with a new master-slave replication mode, can refer to this article: http://bangbangba.blog.51cto.com/3180873/1702294