Linux下的mysql主从配置

主服务器：192.168.110.129

从服务器：192.168.110.130

 

注：主从服务器同步的数据库类型、引擎保持一致

1. 修改主服务器mysql/etc/my.cnf配置

[mysqld]

#打开日志(主机需要打开)

log-bin=mysql-bin

#服务器id

server-id=1

#给从机同步的库

binlog-do-db=数据库名

#自动清理1天前的log文件

expire_logs_days=1

 

2. 修改从服务器mysql的/etc/my.cnf配置

[mysqld]

#服务器id

server-id=2

#要从主机同步的库

replicate-do-db=数据库名

 

3. 保存重启mysql服务

systemctl restart mariadb.service

mariadb.service：这里装的是mariadb；如果是mysql，则改成mysql.service

 

4. 配置主服务器

mysql -uroot -p 进入管理页面，执行：

①创建同步账号

mysql> GRANT REPLICATION SLAVE ON *.* TO '登录名'@'192.168.110.130' IDENTIFIED BY '登录密码';

192.168.110.130：代表只允许指定ip连接；也可换成“%”，则代表允许所有

 

②刷新权限

FLUSH PRIVILEGES;

 

③查看主服务状态

SHOW MASTER STATUS;

 

mysql> SHOW MASTER STATUS;

+------------------+----------+---------------------------------------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB                                | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+---------------------------------------------+------------------+-------------------+

| mysql-bin.000001 |      594 | shenxianyun_console,shenxianyun_portal,test |                  |                   |

+------------------+----------+---------------------------------------------+------------------+-------------------+

1 row in set (0.00 sec)

 

5. 配置从服务器的从数据库

配置和改变slave服务器用于连接master服务器的参数【注意，MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=594是通过前面的主数据库SHOW MASTER STATUS;得到】

 

mysql>CHANGE MASTER TO MASTER_HOST='192.168.110.129',MASTER_USER='同步账号名', MASTER_PASSWORD='密码',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=594;

 

开启SLAVE同步

start slave;

 

查看下slave状态

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: 123.56.217.82

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 594

               Relay_Log_File: iZu1xmovyagZ-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: shenxianyun_console,shenxianyun_portal,test

          Replicate_Ignore_DB:

           Replicate_Do_Table:

当Slave_IO_Running和Slave_SQL_Running都为Yes，才说明主从复制成功

6. 停止SLAVE同步

stop slave;

7. 撤销已经赋予给MySQL同步账户的权限

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

GRANT REPLICATION SLAVE ON *.* TO '用户名'@'192.168.110.130' IDENTIFIED BY '密码';

REVOKE REPLICATION SLAVE ON *.* FROM '用户名'@'192.168.110.130';