-
-
启用二进制日志
[mysqld] log_bin
-
为当前节点设置一个全局惟一的ID号
[mysqld] log_bin server-id=# #为当前节点设置一个全局惟的ID号 log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
说明：
server-id的取值范围 1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1 0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
-
创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass'; -
查看从二进制日志的文件和位置开始进行复制
SHOW MASTER LOGS;
-
-
从节点配置：
-
启动中继日志
[mysqld] server_id=# #为当前节点设置一个全局惟的ID号 log-bin read_only=ON #设置数据库只读，针对supper user无效 relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin relay_log_index=relay-log.index #默认值hostname-relay-bin.index
-
使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mariadb-bin.xxxxxx', MASTER_LOG_POS=#; START SLAVE [IO_THREAD|SQL_THREAD]; SHOW SLAVE STATUS;
-
-
范例：新建主从复制
"保证两个数据库版本一致" `修改主节点配置文件` [root@db02 ~]# cat /etc/my.cnf.d/server.cnf |grep -A 4 '\[mysqld\]' [mysqld] server-id=1 log-basename=master log_bin=mariadb-bin binlog_format=ROW innodb_flush_log_at_trx_commit=2 #优化数据库速度，建议加可以不加 `重启主节点数据库` [root@db02 ~]# systemctl restart mariadb.service `准备主节点复制授权账号` MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.139.%' identified by 'stangj'; `查看记录点` MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.00 sec) `对主节点元数据进行备份` [root@db02 ~]# mysqldump -uroot -proot -F -A --single-transaction --master-data=1 --default-character-set=utf8 > /backup/all.sql `拷贝源数据到重节点` [root@db02 ~]# scp /backup/all.sql 192.168.139.204:/root/ `从节点安装数据库` [root@slaves ~]# yum -y install mariadb mariadb-server `修改从节点配置文件` [root@slaves ~]# grep -A 5 '\[mysqld\]' /etc/my.cnf.d/server.cnf [mysqld] server_id=2 log-bin=mariadb-bin #建议加可以不加 read_only=ON #建议加可以不加 relay_log=relay-log #建议加可以不加 relay_log_index=relay-log.index #建议加可以不加 innodb_flush_log_at_trx_commit=2 #优化数据库速度，建议加可以不加 `从节点启动服务器` [root@slaves ~]# systemctl restart mariadb.service `准备复制工作` #查找到下面信息然后在发过来的all.sql里面进行修改 MariaDB [(none)]> help change master to; CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10; `修改添加如下内容` [root@slaves ~]# grep -C 3 "MASTER_PASSWORD='stangj'" /root/all.sql CHANGE MASTER TO MASTER_HOST='192.168.139.31', MASTER_USER='repluser', MASTER_PASSWORD='stangj', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=245; `在从节点上还原数据` [root@slaves ~]# mysql < /root/all.sql `查看验证相关信息` MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.139.31 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 245 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No .............................. `启动io thread和sql thread线程` MariaDB [(none)]> start slave; `再次查看状态` MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.139.31 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 403 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 689 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........................ `查看线程` MariaDB [(none)]> show processlist; `验证` #repluser以及同步过来了 MariaDB [(none)]> select user,host from mysql.user; +----------+---------------+ | user | host | +----------+---------------+ | root | 127.0.0.1 | | repluser | 192.168.139.% | | root | ::1 | | | db02 | | root | db02 | | | localhost | | root | localhost | +----------+---------------+
-
范例：当master服务器宕机，提升一个slave成为新的master
#找到哪个从节点的数据库是最新，让它成为新master [root@centos8 ~]#cat /var/lib/mysql/relay-log.info 5 ./mariadb-relay-bin.000002 1180 mysql-bin.000002 996 0 #新master修改配置文件，关闭read-only配置 [root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 read-only=OFF log-bin=/data/mysql/logbin/mysql-bin #清除旧的master复制信息 MariaDB [hellodb]>set global read_only=off; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; #在新master上完全备份 [root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql [root@slave1 ~]#scp backup.sql 10.0.0.28: MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000002 | 371 | +--------------------+-----------+ #分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据 #其它所有 slave 重新还原数据库，指向新的master [root@slave2 ~]#vim backup.sql CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=371; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; MariaDB [hellodb]>set sql_log_bin=off; MariaDB [hellodb]>source backup.sql; MariaDB [hellodb]>set sql_log_bin=on; MariaDB [hellodb]>start slave;
-
3.1.3）主从复制相关
-
限制从服务器为只读
read_only=ON #注意：此限制对拥有SUPER权限的用户均无效
文章评论