1、开启两个实例(多实例)
[root@localhost /]# systemctl start mysqld3307
[root@localhost /]# systemctl start mysqld3308
[root@localhost /]# ps -ef |grep mysqld
mysql 1365 1 54 14:28 ? 00:00:05 /application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
mysql 1372 1 46 14:28 ? 00:00:02
2、从主库中创建复制用户
[root@localhost /]# mysql -S /data/3307/mysql.sock
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
注:地址应该是你自己的主库的IP地址,不然后面会有错误
3、备份主库,恢复到从库
[root@localhost /]# backup
[root@localhost /]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/backup/full.sql
[root@localhost backup]# mysql -S /data/3308/mysql.sock
mysql > source /backup/full.sql;
若第二步出现Error:Couldn`t read…
可能是在某库下用了alter table XX discard tablespace
4、从库下编写change master to
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.10.0.51',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000009',
-> MASTER_LOG_POS=444,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
其中:最后第二、第三要从全备里面找,最后一个是重连的次数
[root@localhost /]# vi /backup/full.sql
:set nu
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=444;
这个地方的IP地址也应该是主库的IP地址,每个人不同
5、从库开启自动复制的线程(I0和SQL线程)
mysql> start slave;
6、检查主从复制的状态
mysql>show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7、可能会出现的错误
Slave_IO_Running: Connecting
在主库验证登陆,发现卡了一会
[root@localhost /]# mysql -urepl -p123 -h 10.0.0.51 -P 3307
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.51' (111)
可以明显的知道,这是由于主库的IP地址不对,由于是个人动态DHCP,主库IP地址为192.168.40.139,每个人都不同。
所以在先停掉从库
mysql> stop slave
mysql> reset slave to
然后重新change master to
CHANGE MASTER TO
MASTER_HOST='192.168.40.139',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=1029,
MASTER_CONNECT_RETRY=10;
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1029;
注:不过我个人是又重新全备了