主库：121.43.104.34 mysql版本 [5.5.43-0ubuntu0.14.04.1-log]
从库：121.40.171.96 mysql版本 [5.7.18-0ubuntu0.16.04.1-log]
修改/etc/mysql/my.cnf在[mysqld]下配置
#[必须]启用二进制日志 log-bin = mysql-bin #[必须]服务器唯一ID，默认是1，一般取IP最后一段 server-id = 34
保存后重启mysql:
sudo /etc/init.d/mysql restart
登录mysql
mysql -uroot -p
创建用户syn并授权给从服务器:
grant replication slave on *.* to 'syn'@'121.40.171.96' identified by 'syn';
刷新权限:
flush privileges;
主库状态
show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 17384 | | | +------------------+----------+--------------+------------------+
修改/etc/mysql/mysql.conf.d/mysqld.cnf在[mysqld]下配置
#[不是必须]启用二进制日志 log-bin = mysql-bin #[必须]服务器唯一ID，默认是1，一般取IP最后一段 server-id = 96
保存后重启mysql:
sudo /etc/init.d/mysql restart
登录mysql
mysql -uroot -p
执行
change master to master_host = '121.43.104.34', master_user = 'syn', master_port=3306, master_password='syn', master_log_file = 'mysql-bin.000003', master_log_pos=17384;
开启复制
start slave;
查看从库状态
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 121.43.104.34
Master_User: syn
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 20073
Relay_Log_File: iZbp12rdwfbgblafgipsh5Z-relay-bin.000002
Relay_Log_Pos: 2985
Relay_Master_Log_File: mysql-bin.000003
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: 20073
Relay_Log_Space: 3202
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: 34
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
到这里，主从就算配置完了，在主库create database test;，从库就马上看到新建的数据库。
有个问题，如果主库已经存在的一个数据库，在里面做修改，同步到从库会报错，类似：不存在该库表XXXXX
这时候就得按以下方法，先把主库整个导出再导入到从库，然后开始同步就不会报错。
备份数据库，输入密码
mysqldump -u root -p record_app>/mnt/lost+found/backup/mysql/record_app_`date +%Y%m%d`.sql
拷贝备份的sql文件到从库机器
scp /mnt/lost+found/backup/mysql/record_app_20170526.sql gxx@121.40.171.96:/home/gxx/
主库状态
show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 17385 | | | +------------------+----------+--------------+------------------+
登陆从库，停止复制
stop slave;
创建数据库
create database record_app;
使用数据库
use record_app;
导入数据库
source /home/gxx/record_app_20170526.sql
执行
change master to master_host = '121.43.104.34', master_user = 'syn', master_port=3306, master_password='syn', master_log_file = 'mysql-bin.000003', master_log_pos=17385;
开启同步
start slave;