一、MySQL主从复制搭建

MySQL主从复制搭建主要步骤有：安装mysql软件、Master端配置部署、Slave端配置部署、建立主从同步

1、安装MYSQL

a、环境准备及软件安装

应用需求：

双机热备提供备份，冗余功能

安装环境：

NODE1 主机名 master IP地址 192.168.159.128

NODE2 主机名 slave IP地址 192.168.159.129

VIA IP(漂移IP)10.10.10.100

NODE1为主节点，NODE2为从节点，同步的数据库名fire9

在安装之前请确认下面的安装包不存在

rpm -e mysql-devel-4.1.20-1.RHEL4.1

rpm -e mysql-bench-4.1.20-1.RHEL4.1

rpm -e php-mysql-4.3.9-3.15

rpm -e libdbi-dbd-mysql-0.6.5-10.RHEL4.1

rpm -e mod_auth_mysql-2.6.1-2.2

rpm -e mysql-server-4.1.20-1.RHEL4.1

rpm -e MySQL-python-1.0.0-1.RHEL4.1.i386

rpm -e MyODBC-2.50.39-21.RHEL4.1.i386

rpm -e qt-MySQL-3.3.3-9.3.i386

rpm -e mysqlclient10-devel-3.23.58-4.RHEL4.1.i386

rpm -e mysqlclient10-3.23.58-4.RHEL4.1

rpm -e cyrus-sasl-sql-2.1.19-5.EL4.i386

rpm -e perl-DBD-MySQL-2.9004-3.1.i386

rpm -e mysql-4.1.20-1.RHEL4.1

安装准备：我已经把相关的软件和配置文件都放在工具包里面了

redhat as 4 update4 32位

mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

libnet-1.1.2.1-1.rh.el.um.1.i386.rpm

heartbeat-pils-2.0.4-1.el4.i386.rpm

heartbeat-stonith-2.0.4-1.el4.i386.rpm

heartbeat-2.0.4-1.el4.i386

perl-5.8.8.tar.gz

DBI-1.59.tar.gz

DBD-mysql-4.005.tar.gz

Time-HiRes-01.20.tar.gz

Period-1.20.tar.gz

Convert-BER-1.31.tar.gz

Mon-0.11.tar.gz

mon-0.99.3-47.tar.gz

b、安装MYSQL 主从都要进行安装

# tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz -C /usr/local/

# cd /usr/local/

# mv mysql-5.0.45-linux-i686-icc-glibc23 mysql

# cd mysql

# groupadd mysql

# useradd -g mysql mysql

#passwd mysql

# ./scripts/mysql_install_db --user=mysql

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

# chmod +x /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

# /etc/rc.d/init.d/mysqld start

注：主从都使用yum install 的方式安装系统自带的mysql也可以使用，只是版本会比较低

2、 Master端配置部署

a、在主服务器上的my.cnf配置文件中的[mysqld]节点下添加以下配置

黄色部分为新添加

vi /etc/my.cnf

[mysqld]

server-id=101

log-bin=/var/lib/mysql/mysql-bin.log

log-bin-index=/var/lib/mysql/mysql-bin.index

expire_logs_days=30

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

说明：

log-bin ：给出二进制日志的所有文件基础名

log-bin-index ：给出二进制日志文件的文件名，通常以000001开始，顺序递增。全名：master-bin.000001

server-id ：mysql服务器唯一ID，在主从复制的所有服务器中必须唯一。

b、创建用户，并赋予权限：

create user repl_user;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '******';

设置密码时会遇到报错：

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

解决办法：用select password('你想输入的密码');查询出你的密码对应的字符串

select password('123456');

查出的是*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2、Slave端配置部署

a、配置参数：[mysqld]

黄色部分为新添加

vi /etc/my.cnf

[mysqld]

server-id=102

log-bin=/var/lib/mysql/mysql-relay-bin.log

log-bin-index=/var/lib/mysql/mysql-relay-bin.index

relay_log_purge=on

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

3、建立主从同步

（重建备库也是使用该方法）

建立主从同步可以从主库上导出数据，也可以从已有的从库上导出数据，然后再导入到新的从库中，change master to建立同步。

3.1 、导出数据

在主库上导出数据：

mysqldump -u***-p***-S /data/mysql6001/mysql.sock --default-character-set=utf8 –q --single-transaction --master-data-A > /tmp/all_database.sql

（或者）在从库上导出数据：

mysqldump -u***-p***-S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --dump-slave-A > /tmp/all_database.sql

NOTES：

--master-data和--dump-slave导出的备份中，会包含master_log_file和master_log_pos信息。

例子：

mysqldump -uroot --events --all-databases > /opt/mysql.dump

3.2、从库导入数据

mysql -u*** -p*** --default-character-set=utf8< all_database.sql

例子：

mysql -uroot

3.3、从库与主机建立同步

以下为建立主从同步最基本的6个项：change master to

master_host='xxx.xxx.xxx.xxx', # 主库IP

master_port=6001, # 主库mysqld的端口

master_user='repl', # 主库中创建的有REPLICATION SLAVE权限的用户

master_password='xxxxxxxx', # 该用户的密码

master_log_file='mysql-bin.000xxx', # 已在导入时指定了

master_log_pos=xxxxxx; #已在导入时指定了

start slave;

例子：

master_log_file和master_log_pos通过在主库上使用命令获得：

show master status \G;

在从库上执行：

change master to

master_host='192.168.159.128',

master_port=3306,

master_user='repl_user',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=1071;

start slave;

二、主从复制状态检查及异常处理

1、主从复制状态检查

主库查看binlog情况：show master status\G

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

File: mysql-bin.000303

Position: 18711563

Binlog_Do_DB:

Binlog_Ignore_DB:

在从库上主要是使用以下命令查看从库与主库的同步状态：show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.43.128#主库IP

Master_User: repl#主库复制的用户

Master_Port: 6001#主库mysqld端口

Connect_Retry: 60

Master_Log_File: mysql-bin.000303#io_thread读取主库master_log_file

Read_Master_Log_Pos: 18711563# io_thread读取主库master_log_pos

Relay_Log_File: mysql-relay-bin.000900

Relay_Log_Pos: 18711709

Relay_Master_Log_File: mysql-bin.000303#sql_thread执行主库的master_log_file

Slave_IO_Running: Yes#关键的，io_thread是否running

Slave_SQL_Running: Yes#关键的，sql_thread是否running

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: 18711563#sql_thread执行主库的master_log_pos

Relay_Log_Space: 18711908

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

1 row in set (0.00 sec)

2、IO_thread异常

IO_thread异常，状态往往是Slave_IO_Running: Connecting或NO。

IO_thread是向Master发送请求读取master binlog，如果处于Connecting状态，说明无法正确地与Master进行连接，可能的原因有：

a、网络不通（是否打开防火墙）

b、复制用户的密码不对

c、指定的master_port端口不对

d、master上的mysql-bin.xxxxxx被误删

e、主库磁盘空间满了

通过show slave status\G可以看到相关错误信息，例如：

show slave status\G

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@192.168.43.128:3306' -retry-time: 60

retries: 86400

或者通过错误日志看到相关信息，如：140828 15:47:20 [ERROR] Slave I/O: error connecting to master 'repl@192.168.43.128:3306' -

retry-time: 60 retries: 86400, Error_code: 2003

140828 15:47:21 [Note] Event Scheduler: Loaded 0 events

140828 15:47:21 [Note] /home/mysql/mysql/bin/mysqld: ready for connections.

3、sql_thread异常

sql_thread发生异常，状态就会变为Slave_SQL_Running:NO。

sql_thread发生异常的情况非常多，发生异常后，需要通过以下方法排查和解决：

a、对比主库和从库的二进制日志的情况：

主库：

show master status\G

File: mysql-bin.000303

Position: 18711563

从库：

show slave status\G

Master_Log_File: mysql-bin.000303--IO_thread

Read_Master_Log_Pos: 18711563--IO_thread

Relay_Master_Log_File: mysql-bin.000303--sql_thread

Exec_Master_Log_Pos: 18711163--sql_thread

b、通过show slave status\G查看错误信息：show slave status\G

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database:

'test'. Query: 'insert into test values(1,2,3,4,5,6)'

c、通过错误日志查看错误信息：

140828 16:27:51 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query.

Default database: 'test'. Query: 'insert into test values(1,2,3,4,5,6)',

Error_code: 1062

140828 16:27:51 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062

140828 16:27:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,

and restart the slave SQL thread with "SLAVE START". We stopped at log

'mysql-bin.000303' position 18711163

根据这些报错信息，往往就能够定位到发生异常的原因。如果我们了解产生异常的具体事件，而且能够掌控，可以通过设置sql_slave_skip_counter参数来跳过当前错误。set global sql_slave_skip_counter=1;

a、直接set global sql_slave_skip_counter=n;（n设置很大的值，即：跳过所有错误），

b、设置slave_skip_errors=all;跳过所有类型的错误

c、直接查看主库的binlog，然后在从库上直接执行change master to。

这些方法都会导致主从数据不一致。

如果发现从库与主库差异太大，无法通过手动操作或数据修改重新建立同步。可以参考上述"MySQL主从复制搭建"重新搭建从库。

4、主从复制延迟

主从复制延迟，可能的原因有：

a、主从同步延迟与系统时间的关系，查看主从两台机器间系统时间差

b、主从同步延迟与压力、网络、机器性能的关系，查看从库的io，cpu，mem及网络压力

c、主从同步延迟与lock锁的关系（myisam表读时会堵塞写），尽量避免使用myisam表。一个实例里面尽量减少数据库的数量。

d、主从复制发生异常而中断，过很久之后才发现复制异常。可通过查看master与slave的status估算相差的日志。如果相差太大，则可以考虑重做从库。

5、MYSQL启动报错

MYSQL启动时报错：

Another MySQL daemon already running with the same unix socket

原因多个Mysql进程使用了同一个socket。

两个方法解决：

第一个是立即关机使用命令 shutdown -h now 关机，关机后在启动，进程就停止了。

第二个直接把mysql.sock文件改名即可。也可以删除，推荐改名。

然后就可以启动mysql了。

6、ERROR-2013报错

ERROR-2013报错代码，可能造成的原因是

[ERROR] Slave I/O: error connecting to master 'repl_user@192.168.159.128:3306' - retry-time: 60 retries: 86400, Error_code: 2013

iptables -F

setenforce 0

7、ERROR-1045报错

Last_IO_Errno: 1045

Last_IO_Error: error connecting to master 'repl_user@192.168.159.128:3306' - retry-time: 60 retries: 86400

必须使用以下方法授权