mysql配置主從

centos6.5
mysql：5.6

主從配置注意點

二進位制日誌管理
   每個二進位制日誌大小：max-binlog-size=bytes
    二進位制日誌保留時間：expire-logs-days=days
    複製格式：binlog_format = MIXED 
    同步日誌的事務數：sync-binlog=num
出錯後的處理
    跳過

1. 安裝mysql
http://blog.itpub.net/28282660/viewspace-1571384/

2.mysql的啟動與關閉
[root@root home]# sudo service mysqld start
正在啟動 mysqld：[確定]
[root@root home]# sudo service mysqld status
mysqld (pid  16573) 正在執行
[root@root home]# sudo service mysqld stop
停止 mysqld:  [確定]

3.編輯my.cnf檔案並儲存

不清楚位置可以透過查詢配置檔案位置
find / -name my.cnf

master 關閉服務編輯my.cnf
sudo service mysqld stop
vim /etc/my.cnf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[mysqld]
log-bin=master-bin
log-bin-index=master-bin.index
port=3306
server-id=100
tmpdir=/tmp

4.啟動mysqld服務為master建立資料庫快照
sudo service mysqld start
開啟另外一個會話執行以下語句
FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS

[root@qingmeng001 ~]# mysqldump --all-databases --master-data >/home/20150511dbdump.db
執行完成後退出開啟會話的鎖釋放鎖

5.建立一個用於replication的使用者，並授權
mysql> create user 'rep1' identified by 'rep1';
Query OK, 0 rows affected (0.04 sec)

mysql> grant replication slave on *.* to rep1;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

6.slave關閉服務編輯my.cnf

# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
tmpdir=/tmp
server-id=101
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

slave的server-id與master server-id 不同

啟動slave資料庫

7. 複製匯出的資料快照到slave，並匯入
[root@qingmeng001 home]# scp /home/20150511dbdump.db root@192.168.1.110:/home
root@192.168.1.110's password: 
20150511dbdump.db                                                    100%  613KB 612.7KB/s   00:01    


mysql -h localhost

8.執行以下語句，開始複製
master 執行SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |      405 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


master-bin.000003位置目錄/var/lib/mysql

file 對應 MASTER_LOG_FILE
position 對應 MASTER_LOG_POS
執行下列語句

CHANGE MASTER TO
MASTER_HOST='192.168.1.4',
MASTER_USER='rep1',
MASTER_PASSWORD='rep1',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=317;

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;


9.啟動slave
start slave;


10.檢視slave狀態
mysql> show slave status \G


11.測試
master 執行一下語句
mysql> create database test;
Query OK, 1 row affected (0.00 sec)


mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t (id varchar(20));
Query OK, 0 rows affected (0.07 sec)


mysql> insert into t values(1);
Query OK, 1 row affected (0.03 sec)


mysql> select * from t;
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)


slave檢視是否同步


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)


mysql> select * from t;
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)


針對slave多列的 在插入的時候指定對應列


insert into t value(1,1,2);
insert into t(c1,c2,c3) value(1,1,4);


檢視當前正在寫入的二進位制日誌檔案。
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
             File: master-bin.000005
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

12.reset master 和 reset slave
reset master：刪除了所有二進位制日誌檔案並清空二進位制日誌索引檔案。
reset slave：刪除slave複製所用的所有檔案，並重新開始。