MySQL數(shù)據(jù)庫(kù)的主從配置(多主對(duì)一從)

一、實(shí)驗(yàn)環(huán)境部署

主服務(wù)器 192.168.18.42 端口3306  ==》 從服務(wù)器 192.168.18.44 端口 3306    
主服務(wù)器 192.168.18.43 端口3306  ==》 從服務(wù)器 192.168.18.44 端口 3307

##數(shù)據(jù)庫(kù)，已經(jīng)安裝mysql服務(wù)，安裝部分略。從服務(wù)器上的多個(gè)mysql實(shí)例，請(qǐng)看另一篇帖子《用mysql_multi 實(shí)現(xiàn)一臺(tái)機(jī)器跑多臺(tái)mysql 》

二、部署服務(wù)器

1.在兩臺(tái)主服務(wù)器上賦予從機(jī)權(quán)限，有多臺(tái)叢機(jī)，就執(zhí)行多次（我們這里兩臺(tái)主庫(kù)使用統(tǒng)一帳號(hào)密碼）。

mysql> grant replication slave on *.* to 'backup'@'192.168.18.44' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

2.在主服務(wù)器上配置my.cnf

vi /etc/my.cnf

server-id = 1
log-bin = mysql-bin  #保證binlog可讀
read-only = 0  #主機(jī)，讀寫都可以
#binlog-do-db = test   #需要備份數(shù)據(jù)，多個(gè)寫多行，不寫全部都備份
binlog-ignore-db = mysql #不需要備份的數(shù)據(jù)庫(kù)，多個(gè)寫多行

編輯后重啟數(shù)據(jù)庫(kù) # service mysqld restart

3.配置從庫(kù)服務(wù)器的my.cnf

vi /etc/my.cnf

[mysqld_multi]
mysqld = /mysql/bin/mysqld_safe
mysqladmin = /mysql/bin/mysqladmin

[mysqld1]
port    = 3306
socket  = /tmp/mysql3306.sock
pid-file = /data/mysql/data1/mysql3306.pid
datadir = /data/mysql/data1
skip-name-resolve
log-bin = mysql-bin-3306
log_slave_updates
expire_logs_days = 7
log-error = /data/mysql/data1/mysql3306.err
log_slow_queries = mysql3306-slow.log
long_query_time = 3
query_cache_size = 64M
query_cache_limit = 2M
slave-net-timeout = 10
server-id = 2                     #server id 不要與主庫(kù)的重復(fù)
master-host = 192.168.18.42        #對(duì)應(yīng)主庫(kù)的 ip地址
master-user = backup               # slave 帳號(hào)
master-password = 123456           # 密碼
master-port = 3306                 #主庫(kù)端口
replicate-ignore-db=mysql           #跳過(guò)不備份的庫(kù)
master-info-file = master.1842.info
master-connect-retry = 10
relay-log = relay-bin-1842          #中繼日志
relay-log-index = relay-bin-1842
relay-log-info-file = relay-log-1842.info
default-character-set=gbk
innodb_data_home_dir = /data/mysql/data1
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /data/mysql/data1
innodb_buffer_pool_size = 3072M
innodb_file_per_table
innodb_open_files = 800
#innodb_flush_method = O_DIRECT
innodb_flush_method = O_DSYNC
skip-locking
key_buffer = 32M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 8M
net_buffer_length = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
max_connections = 100
read_only
wait_timeout=288000
interactive_timeout=288000
log-bin-trust-function-creators=1
replicate-ignore-db = test
replicate-ignore-table = mysql.columns_priv
replicate-ignore-table = mysql.host
replicate-ignore-table = mysql.db
replicate-ignore-table = mysql.procs_priv
replicate-ignore-table = mysql.tables_priv
replicate-ignore-table = mysql.user



[mysqld2]
port    = 3307
socket  = /tmp/mysql3307.sock
pid-file = /data/mysql/data2/mysql3307.pid
datadir = /data/mysql/data2
skip-name-resolve
log-bin = mysql-bin-3307
log_slave_updates
expire_logs_days = 7
log-error = /data/mysql/data1/mysql3307.err
log_slow_queries = mysql3307-slow.log
long_query_time = 3
query_cache_size = 64M
query_cache_limit = 2M
slave-net-timeout = 10
server-id = 2                     #server id 不要與主庫(kù)的重復(fù)
master-host = 192.168.18.43        #對(duì)應(yīng)主庫(kù)的 ip地址
master-user = backup               # slave 帳號(hào)
master-password = 123456           # 密碼
master-port = 3306                 #主庫(kù)端口
replicate-ignore-db=mysql           #跳過(guò)不備份的庫(kù)
master-info-file = master.1843.info
master-connect-retry = 10
relay-log = relay-bin-1843
relay-log-index = relay-bin-1843
relay-log-info-file = relay-log-1843.info
default-character-set=gbk
innodb_data_home_dir = /data/mysql/data2
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /data/mysql/data2
innodb_buffer_pool_size = 3072M
innodb_file_per_table
innodb_open_files = 800
#innodb_flush_method = O_DIRECT
innodb_flush_method = O_DSYNC
skip-locking
key_buffer = 32M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 8M
net_buffer_length = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
max_connections = 100
read_only
wait_timeout=288000
interactive_timeout=288000
log-bin-trust-function-creators=1
replicate-ignore-db = test
replicate-ignore-table = mysql.columns_priv
replicate-ignore-table = mysql.host
replicate-ignore-table = mysql.db
replicate-ignore-table = mysql.procs_priv
replicate-ignore-table = mysql.tables_priv
replicate-ignore-table = mysql.user

# 因?yàn)閳D方便就把參數(shù)全部抓出來(lái)了 對(duì)主從有用的也就標(biāo)注的幾個(gè)

4.配置完成后重啟大從服務(wù)器
[root@localhost data1]# mysqld_multi --config-file=/etc/my.cnf --user=root --password=123456 report 1,2
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

5.在從庫(kù)上進(jìn)行chang master ；
1）.在date1上
mysql -uroot -p -S /tmp/mysql3306.sock

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.18.43',
    -> MASTER_PORT=3306,
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='123456'
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

2）。在date2上

mysql -uroot -p -S /tmp/mysql3307.sock

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.18.42',
    -> MASTER_PORT=3306,
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='123456'
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


三、驗(yàn)證：
1.在大從服務(wù)器上

  show slave status\G; 

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

2.在主庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)（18.43上）

mysql> create database haifengtest;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| haifengtest        | 
| mysql              | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

從庫(kù)查看 (mysql3307.sock上）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| haifengtest        | 
| mysql              | 
| test               | 
+--------------------+
4 rows in set (0.00 sec)

四、一種常見的問題。

在show slave status\G；時(shí)發(fā)現(xiàn)下面的問題，（因?yàn)槲沂孪仍趶纳蟿?chuàng)建了該庫(kù)）

  Relay_Master_Log_File: mysql-bin.000005
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: mysql
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1007
                 Last_Error: Error 'Can't create database 'haifengtest'; database exists' on query. Default database: 'haifengtest'. Query: 'create database haifengtest'

如果Replication在Slave上出現(xiàn)上面錯(cuò)誤而停止，一般都期望Slave能忽略這個(gè)錯(cuò)誤，繼續(xù)進(jìn)行同步，而不是重新啟動(dòng)Slave。

　這時(shí)可以使用 SQL_SLAVE_SKIP_COUNTER

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

在驗(yàn)證一次

  show slave status\G; 

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes