Mariadb/mysql Master-slave replication

Mariadb/mysql Master-slave replication

NODE1:172.16.92.1/16 MARIADB Primary Server
NODE2:172.16.92.2/16 mariadb from the server
The above nodes are CentOS 7.1

Configuring the Environment
1. Configure the disc yum source
2. Turn off SELinux and iptables

NODE1:MARIADB Primary Server

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

###### #以下的内容为添加 ########
#二进制变更日志
Log-bin=mysql-bin
#二进制日志格式为混合模式
Binlog_format=mixed
#为主服务器node1的ID值
Server-id = 1
Port = 3306
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 4
Innodb_file_per_table = On
Skip_name_resolve = On
###############################

###### The following content is optional ########
[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 128M
Sort_buffer_size = 128M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout
#############################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d
############### End for My.cnf #################

[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(None)]> grant replication client,replication slave on * * to ' repluser ' @ ' 172.16.92.2 ' identified by ' Replpass ‘;
MariaDB [(None)]> flush privileges;
MariaDB [(None)]> Show Master Status\g
1. Row ***************************
file:mysql-bin.000003
position:497
binlog_do_db:
binlog_ignore_db:

##### write down mysql-bin.000003 and 497, and set the time to relay logs from the server


NODE2:MARIADB from the server

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

########## Add the following content ##########
Log-bin=mysql-bin
Binlog_format=mixed
Server-id = 2
Relay-log = Relay-bin
Log_slave_updates = 1
Read_Only = On

Port = 3306
Skip-external-locking
Key_buffer_size = 256M
Max_allowed_packet = 1M
Table_open_cache = 256
Sort_buffer_size = 1M
Read_buffer_size = 1M
Read_rnd_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 8
Query_cache_size= 16M
Thread_concurrency = 4
Innodb_file_per_table = On
Skip_name_resolve = On
###################################

######### The following content is optional ############
[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 128M
Sort_buffer_size = 128M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout
####################################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d

############# End of My.cnf ###############

[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(None)]> show global variables like '%read_only% ';
| Read_Only | On |

MariaDB [(None)]> show global variables like '%read_only% ' \g
1. Row ***************************
Variable_name:read_only
Value:on

MariaDB [(none)]> change master to master_host= ' 172.16.92.1 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=497,master_connect_retry=5,master_heartbeat_period=2;
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:
master_host:172.16.92.1
Master_user:repluser
master_port:3306
Connect_retry:5
master_log_file:mysql-bin.000003
read_master_log_pos:497
relay_log_file:relay-bin.000001
Relay_log_pos:4
relay_master_log_file:mysql-bin.000003
Slave_io_running:no
Slave_sql_running:no
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:497
relay_log_space:245
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:null
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:0

MariaDB [(None)]> start slave;

MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.92.1
Master_user:repluser
master_port:3306
Connect_retry:5
master_log_file:mysql-bin.000003
read_master_log_pos:497
relay_log_file:relay-bin.000002
relay_log_pos:529
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
........ The rest of the information is slightly ...

MariaDB [(None)]> show Processlist\g
3. Row ***************************
Id:4
User:system User
Host:
Db:null
Command:connect
time:144
State:slave have read all relay log; Waiting for the slave I/O thread to update it
Info:null
progress:0.000
#说明: All relay logs have been received from the node


Node1 on the master node to view this process
MariaDB [(None)]> show Processlist\g
2. Row ***************************
Id:4
User:repluser
host:172.16.92.2:56821
Db:null
Command:binlog Dump
time:212
State:master have sent all binlog to slave; Waiting for Binlog to be updated
Info:null
progress:0.000



To create a database test on the master node for master-Slave synchronization
MariaDB [(None)]> CREATE database TestDB;

From the node can see the TestDB database, the master-slave synchronization success!
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| TestDB |
+--------------------+

And look at the state of the node.
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.92.1
Master_user:repluser
master_port:3306
Connect_retry:5
master_log_file:mysql-bin.000003
read_master_log_pos:584 #497->584
relay_log_file:relay-bin.000002
relay_log_pos:616 #529->616
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes


############# MySQL master-slave copy end ##############



Copy Filter
(1) Implementation of white list based on library
Set on the Slave node
MariaDB [(none)]> stop slave;
MariaDB [(None)]> set global replicate_do_db= ' TestDB ';
MariaDB [(None)]> start slave;
MariaDB [(None)]> show global variables like '%replicat% ';
| replicate_do_db |    TestDB | #只同步该数据库的数据

Create a new database on the master node to see if it can be synchronized.
MariaDB [(None)]> CREATE DATABASE mydb;

The MyDB database is not visible from the node
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| TestDB |
+--------------------+

Insert data in the TestDB of the master node to see if it can be synced.
MariaDB [(None)]> use TestDB;
MariaDB [testdb]> CREATE TABLE t1 (id int);
MariaDB [testdb]> desc t1\g
1. Row ***************************
Field:id
Type:int (11)
Null:yes
Key:
Default:null
Extra:

View the same data from the node, indicating that only one database can now be synchronized from the server
MariaDB [(None)]> use TestDB;
MariaDB [testdb]> Show Tables\g
1. Row ***************************
Tables_in_testdb:t1

MariaDB [testdb]> desc t1\g
1. Row ***************************
Field:id
Type:int (11)
Null:yes
Key:
Default:null
Extra:

This article is from the "8317626" blog, please be sure to keep this source http://8327626.blog.51cto.com/8317626/1711200

Mariadb/mysql master-slave replication