mysql5.5主从复制

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

mysql5.5主从复制

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

安装环境

Distributor ID: CentOS  
Description:    CentOS release 6.5 (Final)  
Release:    6.5  
Codename:   Final  

1、mysql 安装

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

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

#!/bin/bash  
mysqlrootpwd="mima"  
  
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  
  
rm -f /etc/my.cnf    
tar zxvf mysql-5.5.45.tar.gz    
cd mysql-5.5.45/    
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    
make && make install    
    
groupadd mysql    
useradd -s /sbin/nologin -M -g mysql mysql    
    
cp support-files/my-medium.cnf /etc/my.cnf    
sed '/skip-external-locking/i\datadir = /usr/local/mysql/var' -i /etc/my.cnf    
  
sed -i 's:#innodb:innodb:g' /etc/my.cnf    
sed -i 's:/usr/local/mysql/data:/usr/local/mysql/var:g' /etc/my.cnf    
  
  
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql    
chown -R mysql /usr/local/mysql/var    
chgrp -R mysql /usr/local/mysql/.    
cp support-files/mysql.server /etc/init.d/mysql    
chmod 755 /etc/init.d/mysql    
    
cat > /etc/ld.so.conf.d/mysql.conf<<EOF  
/usr/local/mysql/lib    
/usr/local/lib    
EOF  
   
ldconfig    
    
ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql    
ln -s /usr/local/mysql/include/mysql /usr/include/mysql    
if [ -d "/proc/vz" ];then    
ulimit -s unlimited    
fi  
  
/etc/init.d/mysql start    
    
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql    
ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump    
ln -s /usr/local/mysql/bin/myisamchk /usr/bin/myisamchk    
ln -s /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe    
    
/usr/local/mysql/bin/mysqladmin -u root password $mysqlrootpwd    
    
cat > /tmp/mysql_sec_script<<EOF  
use mysql;    
update user set password=password('$mysqlrootpwd') where user='root';    
delete from user where not (user='root') ;    
delete from user where user='root' and password='';    
drop database test;    
DROP USER ''@'%';    
flush privileges;    
EOF  
  
/usr/local/mysql/bin/mysql -u root -p$mysqlrootpwd -h localhost < /tmp/mysql_sec_script    
  
rm -f /tmp/mysql_sec_script    
    
/etc/init.d/mysql restart    
/etc/init.d/mysql stop    

2、master 库

建立binlog存放文件夹

mkdir /data/binlog -p  
chown -R mysql:mysql /data/binlog  

编辑 mysql 配置文件 my.cnf

vim /etc/my.cf      
      
[mysqld]      
log-bin=/data/binlog/mysql-bin.log      
log-bin-index=/data/binlog/mysql-bin.index      
expire_logs_days = 30      
max_binlog_size = 100M  
innodb_file_per_table=1     
server-id       = 1 

创建用户,并赋予权限

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

3、slave 库

建立binlog存放文件夹

mkdir /data/relaylog -p  
chown -R mysql:mysql /data/relaylog  

编辑 mysql 配置文件 my.cnf

[mysqld]    
server-id       = 2   
innodb_file_per_table=1   
relay-log=/data/relaylog/mysql-relay-bin.log    
relay-log-index=/data/relaylog/mysql-relay-bin.index    
relay_log_purge=on    
    
(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 数据库

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

导入 slave 数据库

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

此处导入脚本,就已经在从库中执行了以下操作:

change_master_to
master_log_file=' mysql-bin.000xxx',
master_log_pos=xxxxxx;

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

从库与主机建立同步

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

5、检查状态

主从复制状态检查

主库查看 binlog 情况:

show master status\G  
  
*************************** 1. row ***************************  
            File: mysql-bin.000001  
        Position: 384  
    Binlog_Do_DB:   
Binlog_Ignore_DB:   
1 row in set (0.00 sec) 

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

show slave status\G  
  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 192.168.1.143  
                  Master_User: testuser  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000001  
          Read_Master_Log_Pos: 552  
               Relay_Log_File: mysql-relay-bin.000002  
                Relay_Log_Pos: 698  
        Relay_Master_Log_File: mysql-bin.000001  
          
             Slave_IO_Running: Yes      #这两个变为 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: 552  
              Relay_Log_Space: 854  
              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  
1 row in set (0.00 sec) 

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

mysql master 操作时有时会锁住表

mysql> flush tables with read lock;  
mysql> show master status;  
+---------------+----------+--------------+------------------+  
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+---------------+----------+--------------+------------------+  
| binlog.000003 |      295 |              |                  |   
  
mysql>unlock tables; 

参考-保持主从一致性

在master中

innodb_flush_log_at_trx_commit = 1  
sync_binlog = 1 

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

在slave中

master_info_repository = "TABLE"  
relay_log_info_repository = "TABLE"  
relay_log_recovery = 1  

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

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

Leave a Reply

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

14 + 18 =