centos7 + mariadb主从配置

环境：

主服务器：ceph-node2 192.168.200.130   centos7

从服务器：ceph-node3 192.168.200.136   centos7

主从服务器安装mariadb

[root@ceph-node2 ~]# yum install mariadb mariadb-server -y

[root@ceph-node2 ~]# systemctl start mariadb.service

[root@ceph-node2 ~]# mysql_secure_installation   ##############安装数据库

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

[root@ceph-node2 ~]# mysql -u root -p      ############进入数据库

MariaDB [(none)]> show databases；

创建一个测试数据库DB1：

MariaDB [(none)]> create database DB1;

MariaDB [(none)]> show databases;

赋予数据库DB1连接权限：

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by 'redhat' with grant option;

MariaDB [(none)]> flush privileges;
使用DB1创建一个表：

MariaDB [(none)]> use DB1

MariaDB [DB1]> create table test (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));

MariaDB [DB1]> insert into test values ('abccs','f','1995-10-22','china');

将数据库备份到从服务器上

[root@ceph-node2 ~]# mysqldump -h localhost -u root -p DB1 > 1.sql  

[root@ceph-node2 ~]# scp /root/1.sql root@192.168.200.136:/root
The authenticity of host '192.168.200.136 (192.168.200.136)' can't be established.
ECDSA key fingerprint is SHA256:aVW5+M3kXFDy1pKfJysMRwxGiC1Tnf2nGAR5OP613/U.
ECDSA key fingerprint is MD5:d6:72:71:af:ef:70:08:08:ef:81:9b:ba:e6:37:46:b9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.200.136' (ECDSA) to the list of known hosts.
root@192.168.200.136's password:
1.sql                                                                                                     100% 1926   292.9KB/s   00:00

从服务器按照上面的方法安装mariadb,创建一个空数据库DB1,不需要创建表,为了将主服务器上的mysql还原到从服务器上。

[root@ceph-node3 ~]#yum install mariadb mariadb-server -y

[root@ceph-node3 ~]#systemctl start mariadb.service
[root@ceph-node3 ~]# mysql_secure_installation

[root@ceph-node3 ~]# mysql -u root -p
MariaDB [(none)]> create database DB1;

[root@ceph-node3 ~]# mysql -h localhost -u root -p DB1 < 1.sql

mysql的主从配置：

主服务器配置：

在/etc/my.cnf 添加如下内容：

注：一定要加入到加到mysqld下面，如果加入的位置或者信息不对的话，执行show master status;结果为空；

#设置服务器ID,为1的表示为主服务器；如果服务器没有以下配置，添加，如果有就跳过。
server-id=1
##启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
log_bin=mysql-bin?
##需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行\
binlog-do-db=DB1
##不同步mysql系统数据库
binlog-ignore-db=mysql

重启服务

[root@ceph-node2 ~]#systemctl restart mariadb.service

进入mysql,查看数据库主机的配置文件状态

[root@ceph-node2 ~]# mysql -u root -p

MariaDB [(none)]> show master status;

查看主服务器，出现以上类似信息；如果看不见以下结果或者为空的话，说明my.cnf配置文件配置的有问题，请检查即可。

这里记住File的值：mysql-bin.000001和Position的值：245，后面会用到。

从服务器配置：

配置MySQL从服务器的my.cnf文件;区别就是server-id不一样

在/etc/my.cnf 添加如下内容：

注：一定要加入到加到mysqld下面，如果加入的位置或者信息不对的话，执行show master status;结果为空；

#设置服务器ID,为1的表示为主服务器；如果服务器没有以下配置，添加，如果有就跳过。
server-id=2
##启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
log_bin=mysql-bin?
##需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行\
binlog-do-db=DB1
##不同步mysql系统数据库
binlog-ignore-db=mysql

接下来配置slave，进入从服务器的mysql控制台配置下：

[root@ceph-node3 ~]# mysql -uroot -p
MariaDB [(none)]> slave stop;
MariaDB [DB1]> change master to master_host='192.168.200.130',master_user='root',master_password='123456',master_log_file='mysql-bin?.000001',master_log_pos=245;

说明：change master to master_host='主服务器',master_user='mysql用户名',master_password='mysql服务密码',master_log_file='File值',master_log_pos=Postion值;

=245;

里面填写的信息是是在主服务器上执行showmasterstatus;的信息

[root@ceph-node3 ~]# systemctl restart mariadb

[root@ceph-node3 ~]# mysql -uroot -p

MariaDB [(none)]> show master status;                    

MariaDB [(none)]> slave start;   #########启动slave

最后我们查看主从同步是否正常：

MariaDB [(none)]> show slave status \G;

主要是看 Slave_IO_Running 喝Slave_SQL_Runing 这两个参数是yes就说明主从配置完成。

这里Slave_IO_Running 是 No.

根据 http://www.51testing.com/html/00/130600-243651.html 提供的方法解决

方法如下：

重置slave.

slave stop;

reset slave;

slave start;

重新执行show slave status \G;  正常。

参考：https://www.2cto.com/net/201612/573570.html