mysql主从复制

1. mysql主从复制

1.1 mysql的安装

1.yum remove mysql-libs(中间过程会出现提示,输入y回车即可)

shell> yum remove mysql-libs

2.安装mysql(以下的mysql-VERSION-OS代表mysql的文件名， /path/to/代表mysql文件的路径)，在terminal终端中键入下列命令

shell> groupadd mysqlshell> useradd -r -g mysql mysqlshell> cd /usr/localshell> tar zxvf /path/to/mysql-VERSION-OS.tar.gzshell> ln -s full-path-to-mysql-VERSION-OS mysqlshell> cd mysqlshell> chown -R mysql .shell> chgrp -R mysql .shell> scripts/mysql_install_db --user=mysqlshell> chown -R root .shell> chown -R mysql data

3.在开启mysql服务之前删除文件夹下面的my.cnf,打开new.cnf,修改里面的socket为3306，修改new.cnf的文件名为my.cnf:

port = 3306socket = /tmp/mysql.sock

4.在终端中输入下列命令

shell> bin/mysqld_safe --user=mysql &

5.ps -ef | grep mysqld查看是否开始启动mysql客户端

shell> ps -ef | grep mysqld

6.在终端中输入下列命令

Shell> bin/mysql -uroot

进入mysql客户端输入下列命令进行mysql的root密码以及远程连接权限的修改(注意每条命令后都需要添加’;’)

mysql> use mysql;mysql> UPDATE user SET Password = PASSWORD('root') WHERE user = 'root';mysql> FLUSH PRIVILEGES;mysql> update user set host = '%'  where host ='localhost';mysql> flush privileges;mysql> grant ALL PRIVILEGES ON *.* to root@"%" identified by "mysql" WITH GRANT OPTION;

7.关闭防火墙(也可通过命令打开指定端口，这里没有给出，因为后面需打开多个端口)，在以下命令终端中输入

 shell> service iptables stop

1.2 mysql的数据同步

1.2.1相关准备

主库(master): ip为192.168.1.10 port为3306
从库(slave): ip为192.168.1.11 port为3306
分别按照1.1的步骤在两台机器上安装如上配置的mysql

1.2.2.主库配置

my.cnf

[mysqld]log-bin=mysql-bin    #打开日志(主机需要打开)server-id=1         ＃mysql的唯一标识port = 3306          ＃端口号socket = /tmp/mysql.sock    ＃sock地址sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

1.2.3.从库配置

my.cnf

[mysqld]log_bin=mysql-binserver-id=2socket = /tmp/mysql.socksql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

提示:
在修改完成之后重新启动mysql使配置文件生效。

1.2.4.主库与从库的同步

1.主库锁表只读

mysql>flush  tables  with  read  lock;

2.查看主库状态,并记录

mysql>show  master  status;

3.导出主库数据

shell> mkdir  /opt/backup/  -pshell> mysqldump  -uroot  -p  -S  /tmp/mysql.sock  -A  -B  | gzip  > /opt/backup/mysql_bak.$(date  +%F).sql.gz

note:注意，-A表示备份所有库， -B表示增加user DB和drop等参数（导库时会直接覆盖所有的）。
4.把主库备份的mysql数据迁移到从库

shell> scp /opt/backup/mysql_bak.2016-03-29.sql.gz root@192.168.1.11:/opt/backup/

在从库所在的系统上

shell> cd  /server/backup/ &&  ls -lshell> gzip  -d  mysql_bak.2016-03-29.sqlshell> mysql  -uroot  -p"123123"  <  mysql_bak.2012-05-22.sql

5.设置从属关系
登陆主库并设置数据同步的用户名和密码

shell> mysql  -uroot  -pmysql> select   user();    查看用户mysql> grant  replication  slave  on  *.*  to  'rep'@'192.168.1.%'  identified  by  "123456";  建立用于库复制的账号rep#replication  slave为mysql同步的必须权限，此处不要授权all#*.*表示所有库所有表，库也是可以指定具体的库和表进行复制,如test.test1（test库的test1表）；#'rep'@'192.168.1.%' rep为同步账号，192.168.1.%为授权主机，使用了%表示允许整个192.168.1.0网段以rep用户访问；#identified  by  "123456" , 123456为密码

登陆从库并开启主从同步

shell> mysql  -uroot  -pmysql> CHANGE  MASTER  TO   连接主数据库MASTER_HOST="192.168.1.10",    这里是主库的ipMASTER_PORT=3306,   这里是主库的端口，从库的端口可以和主库不同MASTER_USER="rep",   这里是主库上建立的用于复制的用户repMASTER_PASSWORD="123456",   这里是rep的密码MASTER_LOG_FILE="mysql-bin.000003",  这里是show  master  status时查看到的二进制日志文件名称，注意不能多空格MASTER_LOG_POS=1889;   这里是show  master  status时查看到的二进制日志偏移量，注意不能多空格。mysql> start slave;        开启从属服务mysql> show slave status;  查看从属服务状态

note:

只有当slave_io_running以及slave_sql_running都是yes的状态时，部署成功
6.主库解锁

mysql>unlock  tables;

1.2.5.主从复制的常见问题

question:
slave_io_running为no:
cause:
1).网络有问题，连接不上
2).my.cnf配置文件配置出错
3)由于主从机器复制的一套mysql解压文件,所以在mysql文件夹的data目录下的auto.cnf中的server-uuid相同,报错
solve:
对于原因3可以删除从库的data目录下的auto.cnf文件然后重新运行,执行start slave;(注意删除之前进行备份,以防不测。)

question:
slave_sql_running为no[代表数据库不同步]
cause:
1).程序可能在slave上进行了写操作
2).也可能是slave机器重起后，事务回滚造成的.
solve:
1).重新按照上述的步骤来进行数据库同步
2).

mysql> slave stop;mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;mysql> slave start;

强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用

总结

其实最根本的解决方法是善于利用mysql记录的日志,来详细的分析出错原因。日志的位置在/mycat/data/localhost.err