MARIADB Master-Slave construction and testing

(i) Install the same version of MARIADB. Here I use Yum to install
1) Master:
RPM--import Http://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Echo ' # MariaDB 10.0 CentOS repository list-created 2014-03-15 UTC
# http://mariadb.org/mariadb/repositories/
[MARIADB]
Name = MariaDB
BaseURL = Http://yum.mariadb.org/10.0/centos6-amd64
Gpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb
Gpgcheck=1 ' >>/etc/yum.repos.d/mariadb.repo

Yum Clean All

#Installing MariaDB with yum##########################################################
Yum-y Install Mariadb-server mariadb-client

#加入启动项 ###########################################################################
Chkconfig--levels 235 MySQL on

#start mariadb########################################################################
/etc/init.d/mysql start

#设置mysql密码及相关设置 ##############################################################
Mysql_secure_installation

#移动mysql配置文件
Cp-p/etc/my.cnf/etc/my.cnf.bak
mv/etc/my.cnf/data/conf/
Ln-s/data/conf/my.cnf/etc/

#移动mysql数据库
Cp-rp/var/lib/mysql/var/lib/mysql-bak
mv/var/lib/mysql/data/
Ln-s/data/mysql/var/lib/

# Add Firewall rule
/sbin/iptables-i input-p TCP--dport 3306-j ACCEPT
/etc/rc.d/init.d/iptables Save
/etc/init.d/iptables restart

2) slave Perform the same operation

(ii) Setting up a remote user on the master server allows access to data on the primary server from the server through this user
MariaDB [(None)]> GRANT REPLICATION SLAVE on * * to ' SLAVE ' @ ' 192.168.3.133 ' identified by ' 123456 '; (Allows user slave to copy data on the current master via 192.168.3.133)
MariaDB [(None)]> FLUSH privileges;
(c) Modify the configuration file of the primary database my.cnf, turn on Binlog, and set the value of Server-id (the server_id value can be an integer number (1 ~ 2^31-1), in the same replication group (replicating groups) Must be unique for each server in the server_id, the MySQL service must be restarted after modification
[Email protected] ~]# VI/DATA/CONF/MY.CNF
Log-bin=/data/mysql/mysql-bin.log
Server-id = 1
Binlog-ignore-db=mysql,information_schema,performance_schema (ignoring the synchronized database)
Binlog-do-db=masterslavetest (the name of the database that needs to be replicated, if you copy multiple databases, this option can be set repeatedly)
Replicate-ignore-db=mysql,information_schema,performance_schema (ignoring the synchronized database)
Replicate-do-db=masterslavetest (the name of the database that needs to be replicated, if you copy multiple databases, this option can be set repeatedly)

# Smooth loading of MySQL configuration files
[[Email protected]alhost mysql]# service MySQL Reload
# Check the current binary log name and offset of the primary server to determine the current point, primarily to facilitate copying data from the server.
MariaDB [(None)]> show Master status\g;
# Reset Master
MariaDB [(none)]> reset Master;

(d) Increase the database on the primary server, add data tables, insert data. Then we perform a lock table operation on our master server. View the offset of the current point on the current primary server. We synchronize the data from the current point of time to the slave server
Master
MariaDB [(None)]> CREATE Database masterslavetest; (The primary server adds a database.) Wait, we just use this database to perform master-slave synchronization experiments.
MariaDB [(None)]> use masterslavetest;
MariaDB [masterslavetest]> CREATE TABLE t1 (ID int,name varchar (20));
MariaDB [masterslavetest]> INSERT INTO T1 values (1, ' name1 ');
MariaDB [masterslavetest]> flush tables with read lock; (to prevent data from being out of sync, we read locks on the primary server)
Query OK, 0 rows Affected (0.00 sec)
MariaDB [masterslavetest]> Show Master status\g; (# The offset of the current point is viewed on the master server)
1. Row ***************************
file:mysql-bin.000001
position:779
binlog_do_db:
Binlog_ignore_db:mysql,information_schema,performance_schema
1 row in Set (0.00 sec)

Error:no query specified
(v) Export data on the master server
[Email protected] mysql]# mysqldump-u root-p masterslavetest >/root/masterslavet
Six
1) Configure the slave server
[Email protected] ~]# VI/DATA/CONF/MY.CNF
Log-bin=/data/mysql/mysql-bin.log
Server-id = 100 (not the same as the primary server)
Binlog-ignore-db=mysql,information_schema,performance_schema (ignoring the synchronized database)
Binlog-do-db=masterslavetest (the name of the database that needs to be replicated, if you copy multiple databases, this option can be set repeatedly)
Replicate-ignore-db=mysql,information_schema,performance_schema (ignoring the synchronized database)
Replicate-do-db=masterslavetest (the name of the database that needs to be replicated, if you copy multiple databases, this option can be set repeatedly)
The following four items are the same as the primary server configuration

2) reboot from server
3) Specify the primary server and synchronization point from the server
MariaDB [(none)]> change master to master_host= ' 192.168.3.132 ', master_user= ' slave ', master_password= ' 123456 ', Master_log_file= ' mysql-bin.000001 ', master_log_pos=779;

4) Turn on slave
MariaDB [masterslavetest]> start slave;
5) View slave status
MariaDB [masterslavetest]> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.3.132
Master_user:slave
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:536
relay_log_file:localhost-relay-bin.000002
relay_log_pos:706
relay_master_log_file:mysql-bin.000002
Slave_io_running:yes (Here are a few items where Yes indicates success)
Slave_sql_running:yes
Replicate_do_db:masterslavetest

MARIADB Master-Slave construction and testing