mysql配置主从

mysql配置主从

首先关闭需要配置主从的服务器的防火墙

systemctl disable --now firewalld
setenforce 0

在需要配置的终端上安装mariadb或mysql

这里使用mariad演示

首先要确保需要配置的数据库在配置主从之前数据库的数据相同

具体方法是先把要配置主库的终端加上读锁，避免在备份期间有其他人在写入导致数据不一致，然后做一个全量备份把全量备份产生的文件复制到要配置从库的终端上，恢复数据确保数据与主库一致然后解除主库的锁表状态

//先查看主库有哪些库
[root@master ~]# mysql -uroot -phuangtianen -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| 66666              |
+--------------------+

//再查看从库有哪些库
[root@slave ~]# mysql -uroot -huangtianen -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

//全量备份
//锁住所有的表防止
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出

//备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -huangtianen --all-databases > /opt/all-202309071800.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/
all-202309071800.sql
[root@master ~]# scp /opt/all-202309071800.sql root@192.168.10.131:/opt/
root@192.168.10.131's password:
all-202309071800.sql                              100%  786KB  10.6MB/s   00:00 
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -huangtianen < /opt/all-202309071800.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -huangtianen -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| 66666              |
+--------------------+

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

主库操作

创建账号并给从库授权

mariadb上的操作方法

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.10.131' identified by 'HUANGtianen_666';
MariaDB [(none)]> flush privileges;

或

MySQL上的操作方法

mysql> CREATE USER 'repl'@'192.168.10.131' IDENTIFIED BY 'HUANGtianen_666';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.12.129';
mysql> flush privileges;

在主数据库上操作

[root@master ~]# vi /etc/my.cnf  //这个位置是MySQL服务的配置文件，我现在使用的是mariadb服务
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d  //这里表示mariadb的配置文件在/etc/my.cnf.d下面

进入配置文件

MySQL的配置文件是/etc/my.cnf

mariadb的配置文件是/etc/my.cnf.d/mariadb-server.cnf

//找到这个地方
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
//添加这两行
server-id = 10        //数据库服务器唯一标识符，从库的server-id值必须比主库的大，因为方便以后添加新的数据库
log-bin = mysql_bin   //启用mysql_bin日志

重启服务

重启mariadb

[root@master ~]# systemctl restart mariadb

或

重启mysql服务

systemctl restart mysqld

查看日志文件mysql_bin存不存在

[root@master ~]# ls /var/lib/mysql/                //这个就是日志文件
aria_log.00000001  ibdata1      ibtmp1             mysql_bin.000001  mysql_upgrade_info
aria_log_control   ib_logfile0  multi-master.info  mysql_bin.index   performance_schema
ib_buffer_pool     ib_logfile1  mysql              mysql.sock

查看主库状态

//进入MySQL,然后查看主库状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 

在从库上的操作

进入配置文件

[root@slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf 
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
//添加这两行
server-id = 20        //数据库服务器唯一标识符，从库的server-id值必须比主库的大，因为方便以后添加新的数据库
log-bin = myrelay_bin //启用myrelay_bin日志
//重启mariadb
[root@master ~]# systemctl restart mariadb

查看从库状态

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| myrelay_bin.000001 |      330 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置并启动主从复制（认主过程）

mysql> change master to  
    -> master_host='192.168.10.130',        //确认主库ip
    -> master_user='repl',                  //确认主库授权账户
    -> master_password='HUANGtianen_666',   //确认主库账户密码
    -> master_log_file='mysql_bin.000001',  //确认主库日志文件
    -> master_log_pos=328,                  //确认主库日志位置
Query OK, 0 rows affected (0.003 sec)

开启或关闭主从模式

//开启主从模式
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
//关闭主从模式
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)

查看从库状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.10.130               //主库主机
                   Master_User: repl                         //主库登录的账号
                   Master_Port: 3306                         //主库的端口号 
                 Connect_Retry: 60                           //连接时多长时间更新一次
               Master_Log_File: mysql_bin.000001             //主库日志文件
           Read_Master_Log_Pos: 471                          //主库的日志文件位置
                Relay_Log_File: mariadb-relay-bin.000002     //我的本地记录的日志文件
                 Relay_Log_Pos: 698                          //我的本地记录的日志文件的位置
         Relay_Master_Log_File: mysql_bin.000001             //读取到的主库日志文件的位置
              Slave_IO_Running: Yes                          //Slave_IO_Running线程是否正常工作 
             Slave_SQL_Running: Yes                          //Slave_SQL_Running线程是否正常工作
                                                             //两个线程都是yes才是正常的
.......

验证：

在主库上创建一个数据库看看有没有同步到从库上

主库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

MariaDB [(none)]> create database huangtianen;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huangtianen        |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

从库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huangtianen        |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)