MySQL主从复制

MySQL主从复制：2台服务器构建1主1从架构 (主：master ---> 从：slave)

• master端

1、修改MySQL配置文件

vim /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql57/mysql.sock

[mysqld]
port = 3306
socket = /usr/local/mysql57/mysql.scok
skip-external-locking
key_buffer_size = 256MB
max_allowed_packet = 1MB
table_open_cache = 256
sort_buffer_size = 1MB
read_buffer_size = 1MB
read_rnd_buffer_size = 4MB
myisam_sort_buffer_size = 64MB
thread_cache_size = 8
query_cache_size = 16MB
thread_concurrency = 8
log-bin = mysql-bin
binlog_format = mixed
server-id = 1

[mysqldump]
quick
max_allow_packet = 16MB

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128MB
sort_buffer_size = 128MB
read_buffer = 2MB
write_buffer = 2MB

[mysqlhotcopy]
interactive-timeout

2、创建同步数据库账号

mysql -p
grant replication slave on *.* to 'tongbu'@'%' identified by 'Kaiyuan1201';
show master status;

• slave端

1、修改MySQL配置文件，在文件中加入server-id = 2

vim /etc/my.cnf
server-id = 2

2、slave连接master

change master to master_host = 'master_IP',master_user = 'tongbu',master_password = '123456',master_log_file = 'mysql-bin.000001',master_log_pos = 1212637;

3、启动slave并查看状态

start slave;
show slave status\G;

• 验证结果

1、master端创建数据量及表

mysql> create database test;
mysql> use test;
mysql> create table t1(id char(20),name char(20));

2、slave端查看是否复制成功

mysql> show database;
mysql> use test
mysql> show tables;

• 错误处理

1、重置主从服务器

MySQL主从同步的过程中，可能会因为各种原因出现主库与从库不同步的情况，网上虽然有一些解决办法，但是有时很难彻底解决，重置主从服务器也许不是最快的办法，但却是最安全有效的。

master端：

mysql> stop slave;
mysql> flush tables with read lock;
~$ mysqldump -uroot -p db1 > db1_bak.sql
mysql> reset master;
mysql> unlock tables;
~$ scp ./db1_bak.sql root@slave_IP:/root/db1_bak.sql

slave端：

mysql> drop database db1;
mysql> create database db1;
mysql> source /root/db1_bak.sql;
mysql> reset slave;
mysql> start slave;
mysql> show slave status\G;