CentOS configuration mariadb Master-slave Replication Configuration Tutorial

Environment description

Main Library server: 192.168.71.151,centos 7,mariadb 10 installed, no application data.
1:192.168.71.152,centos 7,mariadb 10 from the library server is installed and no data is applied.
2:192.168.71.153,centos 7,mariadb 10 from the library server is installed and no data is applied.
The mariadb of the 3 servers are running correctly.
The general installation configuration of the MARIADB 10 server is based on the Mariadb/mysql/binary source Pack + authorization detailed on CentOS 6.6.
Common configurations for individual servers

The following actions are performed under the [Mysqld] section of each database server configuration file, and the database configuration file path is assumed to be/etc/my.cnf. There are some instructions on master-slave configuration in/etc/my.cnf, see my.cnf in # Replication master Server (default) and # Replication Slave (Comment out Master Use this) section.
Open the configuration files for each database server my.cnf
Check to make sure that the skip-networking for each server is commented out. Master-slave replication requires the database server to use IP eavesdropping, or use UNIX sockets to monitor, other servers are not accessible.
Specify the bind-address as the binding IP of each server network card.
That is, add bind-address=192.168.71.x after the #skip-networking line of the configuration file, configured on the 192.168.71.151 to bind-address=192.168.71.151, Configured on the 192.168.71.152 to bind-address=192.168.71.152, configured on the 192.168.71.153 as bind-address=192.168.71.153.
Configure SERVER_ID. server_id value is 1 to 2 of 32 square-1 of integers, each server needs to add server_id configuration, the server_id of each server need to ensure uniqueness is different, in practice, usually set to the last of the server IP address, that is, set to server_id =151,server_id=152,server_id=153.
After the configuration is finished, invoke service MySQL reload to reload the configuration file.
Configuring the primary server

The following actions are performed on the/etc/my.cnf of the primary server 192.168.71.151.
To ensure that Log-bin is enabled, that is, Log-bin=mysql-bin is not annotated, Log-bin does not specify a storage directory, is the directory that the default DataDir points to, and the login mariadb shell is viewed by the following command:
MARIADB [(None)]> show variables like ' DataDir ';
+---------------+-----------------+
| variable_name | Value |
+---------------+-----------------+
| DataDir | /var/lib/mysql/|
+---------------+-----------------+
Create an account and give replication permissions
This account is used to copy data from the library from the main library
MARIADB [(None)]> GRANT REPLICATION SLAVE on *.* to ' slave_user ' @ ' 192.168.71.% ' identified by ' Bigs3cret ';
Query OK, 0 rows Affected (0.00 sec)
View the file location of the main library binary log
Main Library Lock table operation, do not let the database write operations
MARIADB [(None)]> FLUSH TABLES with READ LOCK;
Query OK, 0 rows Affected (0.00 sec)
Record the main library log file and its current location
MARIADB [(None)]> show MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000002 |              326 |                  | |
+------------------+----------+--------------+------------------+
Remember the file and position sections, which will be used later
Keep the current MARIADB shell terminal Open, that is, keep the main library locked, and if closing the MARIADB shell will cause the main library to be unlocked
Back up the main library already has data and import it from the library, if there is data in the main library that needs to be backed up and imported into the library. Use the new terminal window or terminal emulator tab SSH to log on to the 192.168.71.151 server and perform the following statements for database backup operations
Mysqldump-uroot-p--all-databases > Databases.sql
Unlock the main library
After the data backup is complete, you can release the locks on the main library:
MARIADB [(None)]> UNLOCK TABLES;
Query OK, 0 rows Affected (0.00 sec)
Actions on from the server

The following operations need to be performed from both the library 192.168.71.152 and the 192.168.71.153.
Import the backup's primary library data
Mysql-uroot-p < Databases.sql
Set Relay-log
MY.CNF file to add a row of relay_log=relay-bin, if not set, by default, by host name + "-relay-bin" Generate relay log.
Setting up master-slave replication
Instructions for configuring the Master library information in my.cnf files after MySQL5.5 and mariadb
Send the door----me.
Some of the other configuration information can be referenced
Cat ' Whereis Mariadb|awk ' {print $ '/support-files/my-large.cnf '} '
MARIADB [(None)]> change MASTER to master_host= ' 192.168.71.151 ', master_port=3306,\
Master_user= ' Slave_user ', master_password= ' Bigs3cret ', \
Master_log_file= ' mysql-bin.000002 ', master_log_pos= 326;
Query OK, 0 rows affected (0.24 sec)
This command completes the following several tasks:
Set the current server as 192.168.71.151 from the library
Provides the user name and password required to copy data from the current database (from the library) to the main library, that is, the
GRANT REPLICATION SLAVE on *.* to ' slave_user ' @ ' 192.168.71.% ' identified by ' Bigs3cret '; Specifies the log file and file location to use when copying the master library from the library. The above main library performs show Master STATUS; Displays the file and position in the result
Open master and slave copy
MARIADB [(None)]> START SLAVE;
Query OK, 0 rows Affected (0.00 sec)
View from library status
MARIADB [(None)]> show SLAVE Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.71.151
Master_user:slave_user
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:326
relay_log_file:relay-bin.000001
relay_log_pos:306
relay_master_log_file:mysql-bin.000002
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:565
relay_log_space:826
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:151
MASTER_SSL_CRL:
Master_ssl_crlpath:
Using_gtid:no
Gtid_io_pos:
1 row in Set (0.00 sec)
The slave_io_running and slave_sql_running in the result must be yes, and if not, they need to be modified according to the error prompted.
Test master-slave replication is normal

Create a table or modify the data on the MARIADB shell of the main library 192.168.71.151 to see if it is updated from the library as well, if you follow the update.
For example, assuming that there is a database newdatabase on the main library, executing on the main library
MARIADB [(None)]> use newdatabase;
Database changed
MARIADB [newdatabase]> CREATE TABLE test (ID int unsigned auto_increment primary key);
Query OK, 0 rows affected (1.07 sec)
On each execution from the library
MARIADB [(None)]> use newdatabase;
Database changed
MARIADB [newdatabase]> Show tables;
+-----------------------+
| Tables_in_newdatabase |
+-----------------------+
| Test |
+-----------------------+
1 rows in Set (0.00 sec)