MYsql 主從複制

一安裝MYSQL

下載下傳mysql-5.5.52-linux2.6-x86_64.tar.gz到/usr/local/src/
cd /usr/local/src/
tar zxvf mysql-5.5.52-linux2.6-x86_64.tar.gz
[root@localhost src]# mv mysql-5.5.52-linux2.6-x86_64 /usr/local/mysql
[root@localhost src]# useradd -s /sbin/nologin mysql
[root@localhost src]# cd /usr/local/mysql
[root@localhost mysql]# mkdir -p /data/mysql ; chown -R mysql:mysql /data/mysql
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
Installing MySQL system tables...
[root@localhost mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@localhost mysql]# vim /etc/my.cnf
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# vim /etc/init.d/mysqld
[root@localhost mysql]# /etc/init.d/mysqld start
[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot

主使用者名： root 密碼 mroot 192.168.128.133

從使用者名：root 密碼 sroot 192.168.128.134

主從同步的使用者名 repl 密碼 123456

配置指令後 /usr/local/mysql/bin/mysql 直接用 mysql 指令

[root@localhost ~]# PATH=$PATH:/usr/local/mysql/bin
[root@localhost ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost ~]# source /etc/profile

主從上都建立資料庫 testdb:

主：

mysql> create database testdb;

主導資料給testdb,先導出到123.sql 再将 123.sql導入給testdb;

[root@localhost mysql]# /usr/local/mysql/bin/mysqldump -uroot mysql > 123.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot testdb < 123.sql

從：

[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# scp [email protected]:/usr/local/mysql/123.sql /usr/local/mysql/
[root@localhost mysql]# mysql -uroot -psroot
[root@localhost mysql]# mysql -uroot -psroot testdb < 123.sql

二

1、主從伺服器分别作以下操作：

1.1、版本一緻

1.2、初始化表，并在背景啟動mysql

1.3、修改root的密碼

主： mysqladmin -uroot password 'mroot'

從： mysqladmin -uroot password 'sroot'

一、修改主伺服器master:

#vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin //[必須]啟用二進制日志

server-id=1 //[必須]伺服器唯一ID，預設是1，一般取IP最後一段

除了這兩行是必須的外，還有兩個參數，你可以選擇性的使用:

binlog-do-db=testdb

binlog-ignore-db=databasename1,databasename2

binlog-do-db=需要複制的資料庫名，多個資料庫名，使用逗号分隔。binlog-ignore-db=不需要複制的資料庫庫名，多個資料庫名，使用逗号分隔。這兩個參數其實用一個就可以啦。

二、修改從伺服器slave:

log-bin=mysql-bin //[不是必須]啟用二進制日志

server-id=2 //[必須]伺服器唯一ID，預設是1，一般取IP最後一段

找到 “server-id = 1” 這一行，改為 “server-id = 2” 總之不能讓這個id和master一樣，否則會報錯。

另外在從上，選擇性的增加如下其中一行，對應于主上增加的那行:

replicate-do-db=testdb

replicate-ignore-db=databasename1,databasename2

三、 /etc/init.d/mysql restart

四、主伺服器：

[root@localhost mysql]# mysql -uroot -pmroot

mysql> grant replication slave on *.* to 'repl'@'192.168.128.134' identified by '123456';

mysql> flush tables with read lock; 先鎖定主資料庫

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      401 | testdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> unlock tables;         // 去從那配完再解鎖

Query OK, 0 rows affected (0.00 sec)

[root@localhost mysql]# iptables -F //主伺服器要關閉防火牆，要不然從會 Slave_IO_Running: Connecting

五、從伺服器：

[root@localhost mysql]# mysql -uroot -psroot

mysql> change master to master_host='192.168.128.133',

-> master_user='repl',master_password='123456', //與上面的grant replication對應。

-> master_log_file='mysql-bin.000004',master_log_pos=401;

六、 mysql> unlock tables; //主那解鎖

七、從伺服器開啟方從服制

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.128.133
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 401
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes     // 這裡要yes 主伺服器要關閉防火牆 iptables -F
            Slave_SQL_Running: Yes     // 這裡要yes
              Replicate_Do_DB: testdb
          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: 401
              Relay_Log_Space: 413
              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: 1
1 row in set (0.00 sec)

測試同步：清空主資料庫testdb 裡的表 db

[root@localhost mysql]# mysql -uroot -pmroot
mysql> use testdb;
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
mysql> truncate table db;
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

此時進從伺服器檢視 db 應該為0

[root@localhost mysql]# mysql -uroot -psroot
mysql> use testdb;
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

遇見問題：

導緻lave_IO_Running 為connecting 的原因主要有以下 3 個方面：1、網絡不通 2、密碼不對 3、pos不對 4、 server-id

發現伺服器本地沒有監聽 3306 ： netstat -an | grep 3306 注掉 skip-networking 就可以了。

當主重新開機MYSQL 後發現從Slave_SQL_Running: No

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=N; (N=1 或大于 1 ) 指讓從跳過多少個事件。因為之前有事件執行失敗。