mysql -S /data/3307/mysql.sock -e "select @@server_id"。//主库 mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id" 要求：从库server-id 一般比主库的大
检查主库二进制日志是否开启
1 2 3 4 5 6 7 8 9 10 11 12
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'" -p Enter password: +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | log_bin | ON | | log_bin_basename | /data/3307/mysql-bin | | log_bin_index | /data/3307/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------+
创建授权复制用户
1 2
[root@db01 ~]# mysql -S /data/3307/mysql.sock -p. //主库 mysql> grant replication slave on *.* to repl@'10.0.2.%' identified by '123';
进行主库数据备份（生产环境下）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
[root@db01 ~]# mysqldump -uroot -p123 -S /data/3307/mysql.sock -A -R -E -F --triggers --master-data=2 --max-allowed-packet=128M --single-transaction |gzip >/tmp/alL_$(date +%F).sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@db01 ~]# ls /tmp/ alL_2020-04-24.sql.gz [root@db01 ~]# gzip -d /tmp/alL_2020-04-24.sql.gz [root@db01 ~]# head /tmp/alL_2020-04-24.sql -- MySQL dump 10.13 Distrib 5.7.26, for linux-glibc2.12 (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.7.26-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
恢复数据到从库（3308）
1 2 3
[root@db01 ~]# mysql -S /data/3308/mysql.sock mysql> set sql_log_bin=0; mysql> source /tmp/alL_2020-04-24.sql
告知从库复制的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14
[root@db01 ~]# cat /tmp/alL_2020-04-24.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=314;
[root@db01 ~]# mysql -S /data/3308/mysql.sock mysql> help change master to CHANGE MASTER TO MASTER_HOST='10.0.2.4', #主库的ip地址 MASTER_USER='repl', #用户名 MASTER_PASSWORD='123', #密码 MASTER_PORT=3307, #主库端口 MASTER_LOG_FILE='mysql-bin.000031', #需要追加的binlog日志 MASTER_LOG_POS=314, #binlog起点，查看全备中的终点 MASTER_CONNECT_RETRY=10; #在主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数。如果主服务器.info文件中的值可以读取则优先使用。如果未设置， 默认值为60。
mysql> show processlist; +----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+ | 18 | repl | db01:16526 | NULL | Binlog Dump | 343 | Master has sent all binlog to slave; waiting for more updates | NULL | 正常状态下： 投递线程 主服务器已将所有binlog发送到从服务器；等待更多更新
//从库线程报错详细信息 Last_IO_Errno: 1593 //io报错的错误号 Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. //IO报错的具体信息
//从库线程状态 Slave_IO_Running: No Slave_SQL_Running: Yes
//从库线程报错详细信息 Last_IO_Errno: 1593 //io报错的号码 Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. //IO报错的具体信息
1.根据从库CHANGE MASTER TO信息手动连接主库，测试是否正常。 [root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3308 --->端口问题 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server [root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307 --->ip问题 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110) [root@db01 ~]# mysql -urepl -p1234 -h 10.0.0.51 -P 3307 --->密码问题 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES) [root@db01 ~]# mysql -urepl1 -p123 -h 10.0.0.51 -P 3307 --->用户名问题 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES) 2.在从库下 stop slave; //停掉主从 reset slave all; //清空主库信息 change master to //重新调整主库参数 start slave; //重新启动线程
从库状态： [root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G " -p Slave_IO_Running: No //断开 Slave_SQL_Running: Yes Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000031' at 314, the last event read from '/data/3307/mysql-bin.000032' at 314, the last byte read from '/data/3307/mysql-bin.000032' at 314.'
狗狗币(DogeCoin)
比特币(BitCoin)
