mysql+centos7+ Master-slave replication

MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL license.
One of the reasons for developing this branch is that after Oracle acquired MySQL, there is a potential risk of shutting MySQL out of the source, so the community uses a branching approach to avoid this risk.
MARIADB is designed to be fully compatible with MySQL, including APIs and command lines, making it easy to be a replacement for MySQL.

Method 1:yum Installation Mariadb

Related commands

The relevant command for the MARIADB database is: Systemctl start mariadb  #启动MariaDBsystemctl stop mariadb  #停止MariaDBsystemctl restart MARIADB  #重启MariaDBsystemctl enable mariadb  #设置开机启动

Normal use of MySQL after startup

Systemctl Start mariadb# Enter mysqlmysql-uroot-p

Method 2: Download Mysql-server package (RPM)

# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm# RPM-IVH mysql-community-release-el7-5.noarch.rpm# Yum Install Mysql-community-server

Configure MySQL

1. Chinese Encoding settings

Edit MySQL configuration file/etc/my.cnf[mysqld]character-set-server=utf8datadir=/var/lib/mysqlsocket=/var/lib/mysql/ Mysql.sockcollation-server=utf8_general_cisecurity riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/ Var/run/mysqld/mysqld.pid[client]default-character-set=utf8[mysql]default-character-set=utf8

2. Authorization Configuration

Remote connection Settings Oh set all permissions for all libraries, all tables, assign permissions to root users for all IP addresses
MySQL > Grant all privileges on * * to [email protected] '% ' identified by ' password ';
#创建用户
MySQL > Create user ' username ' @ '% ' identified by ' password ';
#刷新权限
Flush privileges;

MySQL Master-slave replication

The master-slave replication scheme for the MySQL database is its own feature, and the master-slave replication is not a copy of the database files on the disk, but is replicated to the slave server that needs to be synchronized through the Binlog log.

MySQL database supports one-way, two-way, chain-cascade, and other replication of different business scenarios. During replication, one server acts as the primary server (master), receives content updates from users, and one or more other servers act as slave servers (slave), receive log content from Binlog files on Master, parse out SQL, and re-update to slave. Match the winner from the server data.

The logic of master-slave replication has the following types

One-to-one, one-way master-Slave synchronization mode, only write data on master

A master more from

Dual Master Master replication logical schema, which can write data in Master1 or Master2, or write to colleagues at both ends (special settings)

In the production environment, MySQL master-slave replication is asynchronous replication, that is, not strictly real-time replication, but to the user's experience is real-time.
The MySQL master-slave replication cluster feature makes it possible for MySQL databases to support large-scale high-concurrency reads and writes, and effectively protects data backups of server outages.

Application Scenarios

With replication, when the master server is having a problem, we can manually switch to continue service from the server, where the data and the data at the time of the outage are almost identical.
The copy function can also be used as a data backup, but the backup function from the library is invalidated if the human execution of the Drop,delete statement is deleted.

Principle of Master-slave mechanism implementation

(1) Master changes the record to binary log (these are called binary log events, binary logs event), and (2) slave copies the binary log events of master to its trunk log (relay log); (3) Slave redo the event in the trunk log and change the data to reflect its own.

Master Master Library Configuration

#查看数据库状态systemctl status mariadb# stop Mariadbsystemctl stop mariadb

#修改配置文件
Vim/etc/my.cnf
#修改内容

[Mysqld]
Server-id=1
Log-bin=mysql-bin

#重启mariadb
Systemctl Start mariadb

Master Main Library add from library account

1. New user chaoge for master-slave synchronization, allow login from the library is ' 192.168.178.130 ' Create user ' chaoge ' @ ' 192.168.178.130 ' identified by ' Redhat '; 2.# Digression: If prompt password is too simple not compound strategy add in front add this sentence mysql> set global validate_password_policy=0;3. To grant permission from the library account to the Chaoge copy from the library, Copy on the 192.168.178.130 machine
Grant Replication Slave on * * to ' chaoge ' @ ' 192.168.178.130 ';
#检查主库创建的复制账号
Select User,host from Mysql.user;
#检查授权账号的权限
Show grants for [email protected] ' 192.168.178.130 ';

Enables read-only access to the primary database lock table, prevents data writes, and data replication failures
Flush table with read lock;

4. Check the status of the main library

MariaDB [(None)]> Show Master Status
;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 575 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

File is the binary log file name, and Position is where the log begins. Back from the library will be used back from the library will use the back from the library will use the!!!!!!

5. After locking the table, be sure to open a separate SSH window, export all data of the database,

[[Email Protected]_python ~ 19:32:45] #mysqldump-uroot-p--all-databases >/data/all.sql

6. Ensure that data is exported without data insertion, and then view the main library status

Show master status;

7. After exporting the data, unlock the main library and resume the writable;

Unlock tables;

8. SCP the backed-up exported data to the slave database

scp/data/all.sql [Email protected]:/data/

Slave configuration from Library

1. Set the server-ID value and close the Binlog function parameter database server-id is unique within the master-slave replication system, slave server-id to be different from the main library and other slave libraries, and comment out the binlog parameter of slave.
2. Therefore modify the/etc/my.cnf of the Slave, write
[Mysqld]
Server-id=3
3. Restart the database
Systemctl Restart MARIADB
4. Check the parameters of the Slava from the database
Show variables like ' Log_bin ';
Show variables like ' server_id ';
5. Restore Master Library master data into the slave library
Import data (note the path to the SQL file)
mysql>source/data/all.sql;
Method Two:
#mysql-uroot-p 
6. Configure the replication parameters, slave the configuration of the Master Master library from the library connection
MySQL > Change master to master_host= ' 192.168.178.129 ',
Master_user= ' Chaoge ',
Master_password= ' Redhat ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=575;
7. Start the sync switch from the library to test the master-slave replication situation
Start slave;
8. View replication Status
show slave status\g;

The key to checking the success of master-slave replication is

mysql+centos7+ Master-slave replication