mysql-5.6主從同步配置示例

本文環境

主庫：centos6.5 x64 192.168.0.65 mysql-5.6.29

備庫：centos6.5 x64 192.168.0.66 mysql-5.6.29

一、正常配置方式一

# vi /etc/my.cnf

[mysqld]

log-bin = master-bin

log-bin-index = master-bin.index

binlog_format = mixed

server-id = 1

# service mysqld restart

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000001 | 353 | | | |

1 row in set (0.00 sec)

mysql>

grant replication slave on *.* to 'repl'@'%' identified by '123456';

flush privileges;

說明: 預設隻要server-id不相同即可。

log-bin = mysql-bin

server-id = 11

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

配置說明: 不同步mysql庫可以實作主從庫有不同的帳号權限，經過測試，mysql5.6.29中隻在從庫中配置有效。

其它參數：

binlog-do-db = mydb 僅同步一個資料庫

#replicate-ignore-db = mysql 忽略掉mysql庫,該參數産生很多意外的同步問題，還是不使用。

replicate_wild_ignore_table = mysql.% 忽略掉mysql庫

create database `mydb`;

create table `user` (

`id` varchar(20) not null,

`username` varchar(20) not null,

`password` char(32) not null,

primary key (`id`)

) ;

insert into user values ('1', 'koumm', '123456');

insert into user values ('2', 'zhangsan', '123456');

insert into user values ('3', 'lisi', '123456');

insert into user values ('4', 'li2si', '123456');

insert into user values ('5', 'abc', '123456');

insert into user values ('6', 'tom', '123456');

insert into user values ('7', 'jk', '123456');

insert into user values ('8', 'xb', '123456');

mysql> flush tables with read lock;

query ok, 0 rows affected (0.00 sec)

1 row in set (0.00 sec)

mysql> show master logs;

+-------------------+-----------+

| log_name | file_size |

| master-bin.000001 | 353 |

[root@master ~]# mysqldump -uroot -p -b mydb > mydb.sql

說明：-b參數有建庫語句。

mysql> unlock tables;

query ok, 0 rows affected (0.00 sec)

# mysql -uroot -padmin < mydb.sql

mysql >

change master to

master_host='192.168.0.65',

master_port=3306,

master_user='repl',

master_password='123456',

master_log_file='master-bin.000001',

master_log_pos=353;

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: 192.168.0.65

master_user: repl

master_port: 3306

connect_retry: 60

master_log_file: master-bin.000001

read_master_log_pos: 353

relay_log_file: slave-relay-bin.000002

relay_log_pos: 284

relay_master_log_file: master-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: 353

relay_log_space: 457

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

master_uuid: c8bb22a1-024e-11e6-a1e8-000c29225fa0

master_info_file: /usr/local/mysql/data/master.info

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

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

error:

no query specified

#檢視如下兩個參數為yes，說明從庫運作正常。

slave_sql_running: yes

# 主庫插入一條記錄

mysql> use mydb;

database changed

mysql> select * from stu;

+----+----------+----------+

| id | username | password |

| 1 | koumm | 123456 |

| 2 | zhangsan | 123456 |

| 3 | lisi | 123456 |

| 4 | wangwu | 123456 |

| 5 | zhaoliu | 123456 |

| 6 | zhouqi | 123456 |

6 rows in set (0.00 sec)

mysql>

mysql> insert into stu values ('7', 'tom', '123456');

query ok, 1 row affected (0.05 sec)

mysql> commit;

| 7 | tom | 123456 |

7 rows in set (0.00 sec)

本文隻是配置mysql主從的一個過程。還是有很多需要考慮與改進的位址。

(1) 主從庫同步使用者權限的問題考慮，是直接從主庫同步權限呢，還是單獨考慮從庫的權限。

(2) mysql5.5有半自動主從同步複制

(3) mysql5.6有基于gtid主從複制

二、快速配置從庫方式二

1，主庫備份(全庫備份)

mysqldump -uroot -p -a -b --events --master-data=1 > mydb.sql

全庫備份記錄了資料庫備份時的master-bin.000003', master_log_pos=583位置，在配置從庫時無需要在主庫鎖表，記錄pos位置，解鎖等配置。

[root@localhost ~]# egrep -v "#|\*|--|^$" mydb.sql |head

change master to master_log_file='master-bin.000003', master_log_pos=583;

use `mydb`;

drop table if exists `user`;

create table `user` (

`id` varchar(20) not null,

`username` varchar(20) not null,

`password` char(32) not null,

primary key (`id`)

) engine=innodb default charset=utf8;

lock tables `user` write;

2，從庫建立同步的資料庫, 并導入資料

mysql -uroot -padmin

create database mydb;

mysql -uroot -padmin mydb < mydb.sql

3，配置主從同步

mysql>

change master to

master_host='192.168.0.65',

master_port=3306,

master_user='repl',

master_password='123456';

mysql> start slave;

query ok, 0 rows affected (0.02 sec)

mysql> show slave status \g;

*************************** 1. row ***************************

slave_io_state: waiting for master to send event

master_host: 192.168.0.65

master_user: repl

master_port: 3306

connect_retry: 60

master_log_file: master-bin.000003

read_master_log_pos: 583

relay_log_file: testdb-relay-bin.000004

relay_log_pos: 747

relay_master_log_file: master-bin.000003

slave_io_running: yes

replicate_do_db:

replicate_ignore_db:

replicate_do_table:

replicate_ignore_table:

replicate_wild_do_table:

replicate_wild_ignore_table: mysql.%

last_errno: 0

last_error:

skip_counter: 0

exec_master_log_pos: 583

relay_log_space: 2536

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

master_uuid: c8bb22a1-024e-11e6-a1e8-000c29225fa0

master_info_file: /usr/local/mysql-5.6.29-linux-glibc2.5-x86_64/data/master.info

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

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

三、主從同步簡單管理

1. 停止mysql同步

stop slave io_thread; #停止io程序

stop slave sql_thread; #停止sql程序

stop slave; #停止io和sql程序

2. 啟動mysql同步

start slave io_thread; #啟動io程序

start slave sql_thread; #啟動sql程序

start slave; #啟動io和sql程序

3. 重置mysql同步

reset slave;

清除主從同步參數，它會删除master.info和relay-log.info檔案，以及所有的中繼日志，并啟動一個新的中繼日志。

适用重新再次配置一次從庫的情況。

4. 檢視mysql同步狀态

show slave status;

5. 臨時跳過mysql同步錯誤

確定資料一緻的情況下臨時的跳過這個錯誤操作如下，有可能要多次。

stop slave;

set global sql_slave_skip_counter=1;

start slave;

[mysqld]

#slave-skip-errors=1062,1053,1146 #跳過指定error no類型的錯誤

#slave-skip-errors=all #跳過所有錯誤

這裡出現一次1146報錯，是因為配置binlog-do-db過濾，出現該配置的bug問題，所有要慎用binlog-do-db。

mysql-5.6主從同步配置示例

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述