记一次mariadb主从配置

主IP：10.0.0.21
从IP：10.0.0.22
从IP：10.0.0.23

操作系统：centos7.5

1、安装mariadb（主从主机安装）
yum安装
yum install -y mariadb mariadb-server
启动mariadb
systemctl start mariadb
初始化配置
mysql_secure_installation

[root@localhost ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, 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 MySQL
root user without the proper authorisation.
Set root password? [Y/n]    #是否设置root用户密码，输入y并回车或直接回车
New password:               #设置root用户的密码
Re-enter new password:      #再输入一次你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL 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]   #是否删除匿名用户,生产环境建议删除，所以直接回车
… 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] #是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL 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] #是否删除test数据库,直接回车
- 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] #是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
[root@localhost ~]#

2、mariadb配置（主从主机配置）
vim /etc/my.sql
在[mysql]模块下增加

log-bin=mysql-bin            #开启二进制日志文件
server-id=1                 #本台MySQL服务器ID(主从不能重复)
binlog-ignore-db = mysql  #过滤不同步数据库
binlog-ignore-db = information_schema  #过滤不同步数据库
binlog-ignore-db = performance_schema  #过滤不同步数据库

systemctl restart mariadb #重启生效

3、主数据库配置
grant replication slave on . to “用户名”@’%’ identified by “密码”; #创建用于从服务器登陆的账号

show master status; #显示日志状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000002 |     1104 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

4、从数据库配置
change master to master_host=‘10.0.0.21’,master_user=‘master’,master_password=‘123’,master_log_file=‘mysql-bin.000002’,master_log_pos= 1104;
start slave; #启用同步
show slave status\G; #查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.21
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1104
               Relay_Log_File: mariadb-relay-bin.000007
                Relay_Log_Pos: 1388
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 1104
              Relay_Log_Space: 1968
              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: 0
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: 1
1 row in set (0.00 sec)

5、测试主从同步
在主数据库新建数据库和插入数据，查看从数据库中是否有同步。

6、遇到问题
由于从库2是后增加，show slave status\G时发现报错。

Error 'Can't drop database 'test_db'; database doesn't exist' on query. Default database: 'test_db'. Query: 'drop database test_db'

解决办法 （在从库2中执行）

mysql> slave stop;
#将binglog指针下移一个位置
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

主库执行完授权读取数据用户后，flush privileges刷新使其生效。

7、参考博文
http://blog.sina.com.cn/s/blog_6619c0420102yw11.html
https://www.cnblogs.com/ccku/p/13569310.html