MySQL數據庫主從同步(單臺2實例)

一 前戲
#安裝
yum install -y mysql-server mysql mysql-devel
#不同實例目錄
mkdir /data/{3306,3307} -p  
# 授權
chown -R mysql.mysql /data 

二 配置文件與啟動

#刪除或者重命名原/etc/my.cnf配置文件 
mv /etc/my.cnf /etc/my.cnf.bak
#創建配置文件 vi /data/3306/my.cnf （以下為配置文件內容）
# 配置文件從這里開始
[client]
port      = 3306
socket    = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr   #編譯和二進制安裝按實際目錄填寫路徑（/usr為yum安裝的）
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin  #主從同步的關鍵點，從庫上不需要開啟
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1    #主庫從庫ID 不可相同
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid
# 配置文件到這里結束

=========================================

3 創建啟動腳本 vi /data/3306/mysql
#啟動腳本從這里開始
#!/bin/sh
port=3306
user="root"
pwd="123456"    #按實際密碼填寫
Path="/usr/bin"    #按實際安裝指定mysql可執行程序的路徑（可用which mysql查找）
sock="/data/${port}/mysql.sock"
start_mysql()
{
    if [ ! -e "$sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
stop_mysql()
{
    if [ ! -e "$sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown
   fi
}
restart_mysql()
{
    printf "Restarting MySQL...\n"
    stop_mysql
    sleep 2
    start_mysql
}
case $1 in
start)
    start_mysql
;;
stop)
    stop_mysql
;;
restart)
    restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
#啟動腳本到此結束

chmod +x /data/3306/mysql  #授權可執行

===============================================================

4 為3307也創建一樣的一份，注意把所有3306替換3307  修改不同的server-id
  cp /data/3306/mysql /data/3307/
  cp /data/3306/my.cnf /data/3307/
  sed -i 's/server-id = 1/server-id = 2/g' /data/3307/my.cnf
  sed -i 's/3306/3307/g' /data/3307/my.cnf
  sed -i 's/3306/3307/g' /data/3307/mysql 

三 初始化2個實例
#初始化3306端口數據庫
mysql_install_db --datadir=/data/3306/data --user=mysql
#啟動3306數據庫
/data/3306/mysql start
#修改3306實例登錄密碼
mysqladmin -uroot password '123456' -S /data/3306/mysql.sock
mysql_install_db --datadir=/data/3307/data --user=mysql
/data/3307/mysql start
mysqladmin -uroot password '123456' -S /data/3307/mysql.sock

四 配置主庫
#登錄3306實例（主庫）
mysql -uroot -p123456 -S /data/3306/mysql.sock
#授權同步的帳號密碼
grant replication slave on *.* to rep@'%' identified by'66888888';
#刷新權限
flush privileges;
#退出
exit
#主庫鎖表
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush tables with read lock;"
#查看主庫binlog狀態
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/mysql.log
#備份主庫
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/mysql.sql.gz
#主庫解鎖
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;"
備注：執行完此步驟后不要再操作主服務器MYSQL，防止主服務器狀態值變化

五 配置從庫
#導入主庫的備份
gzip -d /mysql.sql.gz
mysql -uroot -p'123456' -S /data/3307/mysql.sock < /mysql.sql
mysql -uroot -p'123456' -S /data/3307/mysql.sock
#設置同步信息
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.7',       #服務器IP
    -> MASTER_PORT=3306,                #主庫端口
    -> MASTER_USER='rep',               #同步的用戶
    -> MASTER_PASSWORD='66888888',      #同步的用戶密碼
    -> MASTER_LOG_FILE='mysql-bin.000002',  #binlog文件（這個信息在/mysql.log里哦）
    -> MASTER_LOG_POS=424;                  #位置點（這個信息在/mysql.log里哦）

備注：可以一次設置以上信息change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;
#開啟從模式
mysql> start slave;           
#查看狀態
mysql -uroot -p'123456' -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running"
#2個Yes表示成功，如不成功最多可能是上一步帳號同步設置可能有問題。