MySQL学习第四篇：MySQL主从复制

1、按照前一篇安装好两台MySQL ： http://blog.csdn.NET/hwhmh2010/article/details/52984890

2、编辑主从复制安装配置脚本

[root@mysql ~]# vim installmasterslave.sh

#!/bin/bash
export mysqlbinpath="/usr/loca/mysql/bin" #mysql路径
#variables for master
export master_mysql_root_passwd="mima+123"  #mysql主数据库的root密码
export replication_user="copydb"  #用于复制的mysql用户
export replication_passwd="123456" #mysql用户copydb的密码
export master_ip=`ifconfig|grep "inet addr:"|grep -v "127.0.0.1"|cut -d: -f2|awk '{print $1}'`    #mysql主服务器IP
export replication_db=replication_db
#variables for slave
export slave_mysql_root_passwd="mima+123" #mysql从数据库的root密码
export slave_ip="172.16.16.102"               #从服务器IP地址
export slave_ssh_root_passwd="mima+123"  #mysql从数据库的ssh的root密码

${mysqlbinpath}/mysql -h$slave_ip -uroot -p${master_mysql_root_passwd} -e "GRANT REPLICATION SLAVE ON *.* TO '$replication_user'@'%' IDENTIFIED BY '$replication_passwd'";

#create replication user
{
${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} <<EOF
GRANT REPLICATION SLAVE ON *.* TO '$replication_user'@'%' IDENTIFIED BY '$replication_passwd';
FLUSH TABLES WITH READ LOCK;
select sleep(10);
EOF
} &

#export the database sql data.
${mysqlbinpath}/mysqldump -uroot -p${master_mysql_root_passwd} > ${replication_db}.sql

#get the master status info.
export status=`${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} -e"show master status\G"`
export binlogname=`echo "$status" | grep "File" | awk '{print $2}'`
export position=`echo "$status" | grep "Position" | awk '{print $2}'`

#deploy the slave mysql server.
${mysqlbinpath}/mysql -h$slave_ip -uroot -p${slave_mysql_root_passwd} -e"stop slave;CHANGE MASTER TO MASTER_HOST=\"${master_ip}\",MASTER_USER=\"${replication_user}\",MASTER_PASSWORD=\"${replication_passwd}\",MASTER_PORT=3306,MASTER_LOG_FILE=\"${binlogname}\",MASTER_LOG_POS=${position},MASTER_CONNECT_RETRY=10;start slave;select sleep(3);show slave status\G"

3、执行主从安装脚本

[root@mysql ~]# installmasterslave.sh
下面是输出结果：

Warning: Using a password on the command line interface can be insecure.  
Warning: Using a password on the command line interface can be insecure.  
Warning: Using a password on the command line interface can be insecure.  
Warning: Using a password on the command line interface can be insecure.  
Warning: Using a password on the command line interface can be insecure.  
  
+----------+  
| sleep(3) |  
+----------+  
|        0 |  
+----------+  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 172.16.16.107  
                  Master_User: copydb  
                  Master_Port: 3306  
                Connect_Retry: 10  
              Master_Log_File: log_bin.000002  
          Read_Master_Log_Pos: 1983  
               Relay_Log_File: mysql-relay-bin.000002  
                Relay_Log_Pos: 281  
        Relay_Master_Log_File: log_bin.000002  
             Slave_IO_Running: 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: 1983  
              Relay_Log_Space: 454  
              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: 107  
                  Master_UUID: 35259c7a-9f2e-11e6-a0d8-000c2964dfae  
             Master_Info_File: /data/master.info  
                    SQL_Delay: 0  
          SQL_Remaining_Delay: NULL  
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
           Master_Retry_Count: 86400  
                  Master_Bind:   
      Last_IO_Error_Timestamp:   
     Last_SQL_Error_Timestamp:   
               Master_SSL_Crl:   
           Master_SSL_Crlpath:   
           Retrieved_Gtid_Set:   
            Executed_Gtid_Set:   
                Auto_Position: 0