Mariadb/mysql Master-slave replication

Source: Internet
Author: User
Tags rehash

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.