***************************************************
新手,简单易懂,整的明白,明白的。。。。
***************************************************
1、保证两个数据库中的库和数据是一致的;
2、在主数据中创建一个同步账号(可不创建使用现有的),如果仅仅为了主从复制创建账号,只需要授予REPLICATION SLAVE权限。
1)、创建一个账号,账号:test 密码: 123456
Mysql>CREATE USER 'test'@'%' IDENTIFIED BY '123456';
Mysql>FLUSH PRIVILEGES;
Mysql>GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
3、关闭主、从服务器mysql
systemctl stop mysqld.service
4、修改主服务器配置:
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=167 #[必须]服务器唯一ID,默认是1,一般取IP最后一段
5、修改从服务器配置:
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=42 #[必须]服务器唯一ID,默认是1,一般取IP最后一段
6、启动主、从服务器mysql
systemctl start mysqld.service
7、查看主服务器mysql状态:
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
8、在从服务器里配置连接主服务器的信息:
进入mysql
mysql>stop slave; #停止从服务器复制功能
mysql>change master to master_host='192.168.1.167', master_port=3306, master_user='test', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=328;
mysql>start slave; #启动从服务器复制功能
说明:
master_host:主服务器IP
master_port:主服务器mysql端口
master_user:主服务器授权的mysql用户名
master_password:主服务器授权的mysql密码
master_log_file:主服务器mysql状态,File
master_log_pos:主服务器mysql状态,Position
9、查看状态:
mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.167
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 328
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 167
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.002 sec)
说明:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都是yes就说明成功了。
9.1、若 Slave_SQL_Running: no 请重复执行以下内容,直至yes:
mysql>stop slave;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>start slave;
10、测试:主服务器开始数据操作,查看从服务器是否改变
--------------------------------
!!!仅供参考!!!