Mysql主从搭建
创建用户并分配权限
mysql> create user 'dante'@'mysql002' identified by '123456';Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to 'dante'@'mysql002' identified by '123456';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 698 | test1 | mysql | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)从节点mysql002：
配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf#服务器IDserver-id = 2重启服务
service mysql restart设置连接信息
mysql> change master to master_host='mysql001', -> master_user='dante', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_port=3306, -> master_log_pos=2005, -> master_connect_retry=10;Query OK, 0 rows affected, 2 warnings (0.04 sec)启动slave
mysql> start slave;Query OK, 0 rows affected (0.01 sec)查看连接信息
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql001 Master_User: dante Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 154 Relay_Log_File: dante-VirtualBox-relay-bin.000003 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000005 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: 154 Relay_Log_Space: 751 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: 0Master_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: 1 Master_UUID: 7a04fb5e-3db5-11e6-abd3-08002703bba1 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)这个时候就能看到同步过来的表了。
错误总结
整个过程就遇到了一个问题，就是连接主库的问题，一直连接不上，这种情况首先考虑的就是防火墙的问题。由于是16.04，我用ufw关闭了防火墙，打开了3306端口。
# nmap -p 22 mysql001Starting Nmap 7.01 ( https://nmap.org ) at 2016-06-29 14:22 CSTNmap scan report for mysql001 (10.1.196.96)Host is up (0.00045s latency).PORT STATE SERVICE22/tcp open sshMAC Address: 08:00:27:03:BB:A1 (Oracle VirtualBox virtual NIC)Nmap done: 1 IP address (1 host up) scanned in 0.48 seconds用nmap扫也没问题了，但是还是连不上。
Slave_IO_Running: Connecting查看一下mysql的日志，也报错为连接失败
2016-06-29T06:22:45.985444Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 19, Error_code: 20032016-06-29T06:22:55.986779Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 20, Error_code: 20032016-06-29T06:23:05.987615Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 21, Error_code: 20032016-06-29T06:23:15.988919Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 22, Error_code: 20032016-06-29T06:23:25.990153Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 23, Error_code: 20032016-06-29T06:23:35.991296Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 24, Error_code: 20032016-06-29T06:23:45.992279Z 10 [ERROR] Slave I/O for channel '': error connecting to master 'dante@mysql001:3306' - retry-time: 10 retries: 25, Error_code: 2003这就是典型的连接失败。这时候我想起来是不是和绑定的地址有关，再看一下mysql的配置文件，这次改了一个参数。
#bind-address = 127.0.0.1bind-address = mysql001