mysql5.5主从复制

Posted on Posted in mysql
Tips: 本文创建于2015年10月21日,已超过 2 年,内容或图片可能已经失效!

mysql5.5的主从复制和5.1以前的不一样了,备份下配置方法。
安装环境

  1. Distributor ID: CentOS  
  2. Description:    CentOS release 6.5 (Final)  
  3. Release:    6.5  
  4. Codename:   Final  

1、mysql 安装

我这里是编译安装的 [mysql-5.5.45.tar.gz][1]

安装脚本以前有,直接复制过来

  1. #!/bin/bash  
  2. mysqlrootpwd="mima"  
  3.   
  4. yum -y install patch make cmake gcc gcc-c++ gcc-g77 flex bison file libtool libtool-libs autoconf kernel-devel libjpeg libjpeg-devel libpng libpng-devel libpng10 libpng10-devel gd gd-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glib2 glib2-devel bzip2 bzip2-devel libevent libevent-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel vim-minimal nano fonts-chinese gettext gettext-devel ncurses-devel gmp-devel pspell-devel unzip libcap  
  5.   
  6. rm -f /etc/my.cnf    
  7. tar zxvf mysql-5.5.45.tar.gz    
  8. cd mysql-5.5.45/    
  9. cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1    
  10. make && make install    
  11.     
  12. groupadd mysql    
  13. useradd -s /sbin/nologin -M -g mysql mysql    
  14.     
  15. cp support-files/my-medium.cnf /etc/my.cnf    
  16. sed '/skip-external-locking/i\datadir = /usr/local/mysql/var' -i /etc/my.cnf    
  17.   
  18. sed -i 's:#innodb:innodb:g' /etc/my.cnf    
  19. sed -i 's:/usr/local/mysql/data:/usr/local/mysql/var:g' /etc/my.cnf    
  20.   
  21.   
  22. /usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql    
  23. chown -R mysql /usr/local/mysql/var    
  24. chgrp -R mysql /usr/local/mysql/.    
  25. cp support-files/mysql.server /etc/init.d/mysql    
  26. chmod 755 /etc/init.d/mysql    
  27.     
  28. cat > /etc/ld.so.conf.d/mysql.conf<<EOF  
  29. /usr/local/mysql/lib    
  30. /usr/local/lib    
  31. EOF  
  32.    
  33. ldconfig    
  34.     
  35. ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql    
  36. ln -s /usr/local/mysql/include/mysql /usr/include/mysql    
  37. if [ -d "/proc/vz" ];then    
  38. ulimit -s unlimited    
  39. fi  
  40.   
  41. /etc/init.d/mysql start    
  42.     
  43. ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql    
  44. ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump    
  45. ln -s /usr/local/mysql/bin/myisamchk /usr/bin/myisamchk    
  46. ln -s /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe    
  47.     
  48. /usr/local/mysql/bin/mysqladmin -u root password $mysqlrootpwd    
  49.     
  50. cat > /tmp/mysql_sec_script<<EOF  
  51. use mysql;    
  52. update user set password=password('$mysqlrootpwd') where user='root';    
  53. delete from user where not (user='root') ;    
  54. delete from user where user='root' and password='';    
  55. drop database test;    
  56. DROP USER ''@'%';    
  57. flush privileges;    
  58. EOF  
  59.   
  60. /usr/local/mysql/bin/mysql -u root -p$mysqlrootpwd -h localhost < /tmp/mysql_sec_script    
  61.   
  62. rm -f /tmp/mysql_sec_script    
  63.     
  64. /etc/init.d/mysql restart    
  65. /etc/init.d/mysql stop    

2、master 库

建立binlog存放文件夹

  1. mkdir /data/binlog -p  
  2. chown -R mysql:mysql /data/binlog  

编辑 mysql 配置文件 my.cnf

  1. vim /etc/my.cf      
  2.       
  3. [mysqld]      
  4. log-bin=/data/binlog/mysql-bin.log      
  5. log-bin-index=/data/binlog/mysql-bin.index      
  6. expire_logs_days = 30      
  7. max_binlog_size = 100M  
  8. innodb_file_per_table=1     
  9. server-id       = 1      

创建用户,并赋予权限

  1. (sql)select password('testpasswd');    >    *CA2DC8B6C2CC6ACB3DE44D56C0BF821DBF383E0B   
  2. GRANT REPLICATION SLAVE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*CA2DC8B6C2CC6ACB3DE44D56C0BF821DBF383E0B';  

3、slave 库

建立binlog存放文件夹

  1. mkdir /data/relaylog -p  
  2. chown -R mysql:mysql /data/relaylog  

编辑 mysql 配置文件 my.cnf

  1. [mysqld]    
  2. server-id       = 2   
  3. innodb_file_per_table=1   
  4. relay-log=/data/relaylog/mysql-relay-bin.log    
  5. relay-log-index=/data/relaylog/mysql-relay-bin.index    
  6. relay_log_purge=on    
  7.     
  8. (sql)reset slave #防止出现错误 ERROR 1201 (HY000) at line 22: Could not initialize master info structure; more error messages can be found in the MySQL error log    

4、从库与主机进行交互和准备

导出 master 数据库

  1. mysqldump -uroot -pmima -S /tmp/mysql.sock --default-character-set=utf8 -q --single-transaction --master-data -A > all_database.sql  

导入 slave 数据库

  1. mysql -uuser -ppasswd --default-character-set=utf8 < all_database.sql  

此处导入脚本,就已经在从库中执行了以下操作:
change_master_to
master_log_file=' mysql-bin.000xxx',
master_log_pos=xxxxxx;

  1. mysql -uroot -pmima --default-character-set=utf8 < all_database.sql  

从库与主机建立同步

  1. (sql)change master to master_host="192.168.1.143",master_port=3306,master_user="testuser",master_password="testpasswd";  
  2. (sql)start slave  

5、检查状态

主从复制状态检查
主库查看 binlog 情况:

  1. show master status\G  
  2.   
  3. *************************** 1. row ***************************  
  4.             File: mysql-bin.000001  
  5.         Position: 384  
  6.     Binlog_Do_DB:   
  7. Binlog_Ignore_DB:   
  8. 1 row in set (0.00 sec)  

在从库上主要是使用以下命令查看从库与主库的同步状态:

  1. show slave status\G  
  2.   
  3. *************************** 1. row ***************************  
  4.                Slave_IO_State: Waiting for master to send event  
  5.                   Master_Host: 192.168.1.143  
  6.                   Master_User: testuser  
  7.                   Master_Port: 3306  
  8.                 Connect_Retry: 60  
  9.               Master_Log_File: mysql-bin.000001  
  10.           Read_Master_Log_Pos: 552  
  11.                Relay_Log_File: mysql-relay-bin.000002  
  12.                 Relay_Log_Pos: 698  
  13.         Relay_Master_Log_File: mysql-bin.000001  
  14.           
  15.              Slave_IO_Running: Yes      #这两个变为 yes 就对了  
  16.             Slave_SQL_Running: Yes  
  17.               
  18.               Replicate_Do_DB:   
  19.           Replicate_Ignore_DB:   
  20.            Replicate_Do_Table:   
  21.        Replicate_Ignore_Table:   
  22.       Replicate_Wild_Do_Table:   
  23.   Replicate_Wild_Ignore_Table:   
  24.                    Last_Errno: 0  
  25.                    Last_Error:   
  26.                  Skip_Counter: 0  
  27.           Exec_Master_Log_Pos: 552  
  28.               Relay_Log_Space: 854  
  29.               Until_Condition: None  
  30.                Until_Log_File:   
  31.                 Until_Log_Pos: 0  
  32.            Master_SSL_Allowed: No  
  33.            Master_SSL_CA_File:   
  34.            Master_SSL_CA_Path:   
  35.               Master_SSL_Cert:   
  36.             Master_SSL_Cipher:   
  37.                Master_SSL_Key:   
  38.                  
  39.         Seconds_Behind_Master: 0    #从库的延迟  
  40.           
  41. Master_SSL_Verify_Server_Cert: No  
  42.                 Last_IO_Errno: 0  
  43.                 Last_IO_Error:   
  44.                Last_SQL_Errno: 0  
  45.                Last_SQL_Error:   
  46.   Replicate_Ignore_Server_Ids:   
  47.              Master_Server_Id: 1  
  48. 1 row in set (0.00 sec)  

附上一份网络找的[MySQL主从复制、搭建、状态检查、中断排查及备库重做 实战手册.pdf][2]

mysql master 操作时有时会锁住表

  1. mysql> flush tables with read lock;  
  2. mysql> show master status;  
  3. +---------------+----------+--------------+------------------+  
  4. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  5. +---------------+----------+--------------+------------------+  
  6. | binlog.000003 |      295 |              |                  |   
  7.   
  8. mysql>unlock tables;  

参考-保持主从一致性

> 在master中

  1. innodb_flush_log_at_trx_commit = 1  
  2. sync_binlog = 1  

保证每次事务提交后,都能实时刷新到磁盘中,尤其是确保每次事务对应的binlog都能及时刷新到磁盘中

> 在slave中

  1. master_info_repository = "TABLE"  
  2. relay_log_info_repository = "TABLE"  
  3. relay_log_recovery = 1  

前两个选项的作用是:确保在slave上和复制相关的元数据表也采用InnoDB引擎,受到InnoDB事务安全的保护,而后一个选项的作用是开启relay log自动修复机制,发生crash时,会自动判断哪些relay log需要重新从master上抓取回来再次应用,以此避免部分数据丢失的可能性。

[1]: http://liuhonghe.me/download/mysql-5.5.45.tar.gz
[2]: http://liuhonghe.me/wp-content/uploads/2015/10/2653600808.pdf

» 转载请注明来源:若我若鱼 » mysql5.5主从复制

Leave a Reply

Your email address will not be published. Required fields are marked *

3 × five =