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