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

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

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

[root@mysql ~]# vim installmasterslave.sh

#!/bin/bashexport mysqlbinpath="/usr/loca/mysql/bin" #mysql路径#variables for masterexport 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主服务器IPexport replication_db=replication_db#variables for slaveexport 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} <<EOFGRANT 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