乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

MYSQL數(shù)據(jù)庫MySQL5.6 Replication主從復(fù)制（讀寫分離) 配置完整版

作者：VEPHP   時間 2017-10-22

《MYSQL數(shù)據(jù)庫MySQL5.6 Replication主從復(fù)制（讀寫分離) 配置完整版》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫MySQL5.6 Replication主從復(fù)制（讀寫分離) 配置完整版，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL數(shù)據(jù)庫MySQL5.6主從復(fù)制(讀寫分別)教程

MYSQL數(shù)據(jù)庫1、MySQL5.6開端主從復(fù)制有兩種方式：

MYSQL數(shù)據(jù)庫基于日志(binlog)；
基于GTID(全局事務(wù)標(biāo)示符).

MYSQL數(shù)據(jù)庫必要注意的是：GTID方式不支持臨時表!所以如果你的業(yè)務(wù)系統(tǒng)要用到臨時表的話就不要考慮這種方式了,至少目前最新版本MySQL5.6.12的GTID復(fù)制還是不支持臨時表的.

MYSQL數(shù)據(jù)庫所以本教程主要是告訴年夜家如何通過日志(binlog)方式做主從復(fù)制!

MYSQL數(shù)據(jù)庫2、MySQL官方提供的MySQL Replication教程：

MYSQL數(shù)據(jù)庫http://dev.mysql.com/doc/refman/5.6/en/replication.html

MYSQL數(shù)據(jù)庫第一步：準(zhǔn)備工作

MYSQL數(shù)據(jù)庫主服務(wù)器: 192.168.1.100
從服務(wù)器: 192.168.1.101

MYSQL數(shù)據(jù)庫MySQL軟件版本：

MYSQL數(shù)據(jù)庫MySQL-server-advanced-5.6.18-1.el6.x86_64.rpm
MySQL-cient-advanced-5.6.18-1.el6.x86_64.rpm

MYSQL數(shù)據(jù)庫第二步：在主服務(wù)器和從服務(wù)器上安裝MySQL數(shù)據(jù)庫軟件

MYSQL數(shù)據(jù)庫安裝辦法,請參見 /article/82542.htm

MYSQL數(shù)據(jù)庫MySQL數(shù)據(jù)庫軟件安裝完成后,不要急著做mysql啟動操作.建議把mysql初始化生成的/usr/my.cnf
(如果是從源文件編譯安裝時,路徑應(yīng)該是在/usr/local/mysql/mysql.cnf)刪除,然后把優(yōu)化好的mysql
配置文件my.cnf放到/etc下.
第三步：改動主數(shù)據(jù)庫的配置文件/usr/my.cnf

代碼如下:

[mysqld]
?
server-id=1
log-bin=mysqlmaster-bin.log
sync_binlog=1

MYSQL數(shù)據(jù)庫innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
?
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
?
lower_case_table_names=1
log_bin_trust_function_creators=1

MYSQL數(shù)據(jù)庫第四步：改動從數(shù)據(jù)庫配置文件/usr/my.cnf
?

代碼如下:

server-id=2
log-bin=mysqlslave-bin.log
sync_binlog=1
innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
?

MYSQL數(shù)據(jù)庫sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1

MYSQL數(shù)據(jù)庫第五步：在主數(shù)據(jù)庫和從數(shù)據(jù)庫服務(wù)器上分離執(zhí)行以下命令重新啟動主數(shù)據(jù)庫和從數(shù)據(jù)庫

代碼如下:

[root@master ~]# service mysql restart
[root@slave ~]# service mysql restart

MYSQL數(shù)據(jù)庫第六步：在主數(shù)據(jù)庫上創(chuàng)立用于主從復(fù)制的賬戶

代碼如下:

[root@master ~]# mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

MYSQL數(shù)據(jù)庫注意：以上敕令中的IP地址,是從數(shù)據(jù)庫服務(wù)器的IP地址.

MYSQL數(shù)據(jù)庫第七步：主數(shù)據(jù)庫鎖表(制止再插入數(shù)據(jù)以獲取主數(shù)據(jù)庫的的二進(jìn)制日志坐標(biāo))
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
?

MYSQL數(shù)據(jù)庫第八步：查看主數(shù)據(jù)庫的狀態(tài)(并記載下File字段和Position字段的值,在配置從服務(wù)器時有用到)

MYSQL數(shù)據(jù)庫mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File????????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000004 |????? 327 |????????????? |????????????????? |????????????????? |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MYSQL數(shù)據(jù)庫第九步：創(chuàng)建主數(shù)據(jù)庫的快照文件
[root@master ~]# cd /usr/bin/
# ./mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases --triggers --routines --events >>/mnt/windows/all.sql
上面命令中的紅色部分,是一個共享目錄,這個目錄可以同時被主數(shù)據(jù)庫服務(wù)器和從數(shù)據(jù)庫服務(wù)器拜訪到.
如果沒有這樣的共享目錄,可以將all.sql放在其它任何目錄下,然后使用scp命令復(fù)制到遠(yuǎn)程從數(shù)據(jù)庫服務(wù)器的某個目錄中
這條命令的執(zhí)行時間根據(jù)數(shù)據(jù)量的不同,會有所不同,如果主數(shù)據(jù)庫的數(shù)據(jù)量很大,可能需要很長時間,那么在這種情況下,就最好在晚上沒有業(yè)務(wù)的時候進(jìn)行這個操作,否則第七步中的鎖表操作會對業(yè)務(wù)系統(tǒng)造成很大的影響
第十步：解鎖主數(shù)據(jù)庫的鎖表操作
[root@master ~]# mysql -uroot -p??? (本命令在主數(shù)據(jù)庫服務(wù)器上執(zhí)行)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
第十一步：在從數(shù)據(jù)庫服務(wù)器上導(dǎo)入第七步創(chuàng)建的快照文件到從數(shù)據(jù)庫中
[root@slave ~]# mysql -uroot -p -h127.0.0.1 -P3306 < /mnt/windows/all.sql
第十二步：在從數(shù)據(jù)庫服務(wù)器上設(shè)置主數(shù)據(jù)庫服務(wù)器向從數(shù)據(jù)庫服務(wù)器同步
[root@slave ~]# mysql -uroot -p
mysql> change master to master_host = '192.168.1.100',master_user='repl',master_password='111111',master_log_file='mysqlmaster-bin.000004',master_log_pos=327;
注意：紅色部分的值,是在第八步中查出來的,這里不能弄錯了
第十三步：啟動從數(shù)據(jù)庫復(fù)制線程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
第十四步：查詢從數(shù)據(jù)庫的復(fù)制線程狀態(tài)

代碼如下:

mysql> show slave status \G
*************************** 1. row ***************************
????????????? Slave_IO_State: Waiting for master to send event
????????????????? Master_Host: 192.168.1.100
????????????????? Master_User: repl
????????????????? Master_Port: 3306
??????????????? Connect_Retry: 60
????????????? Master_Log_File: mysqlmaster-bin.000004
????????? Read_Master_Log_Pos: 327
????????????? Relay_Log_File: slave-relay-bin.000002
??????????????? Relay_Log_Pos: 289
??????? Relay_Master_Log_File: mysqlmaster-bin.000004
????????? 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: 327
????????????? Relay_Log_Space: 462
????????????? 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: 1
????????????????? Master_UUID: 2e5e1b22-f0a9-11e3-bbac-000c297799e0
??????????? Master_Info_File: /var/lib/mysql/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
1 row in set (0.00 sec)

MYSQL數(shù)據(jù)庫如果Slave_IO_Running和Slave_SQL_Running兩項都為yes,就表現(xiàn)主從復(fù)制配置成功了.
下面可以開始測試配置是否成功了,首先在主數(shù)據(jù)庫的test數(shù)據(jù)庫中新建一張表,然后插入幾條數(shù)據(jù),然后到從數(shù)據(jù)庫看看是否同步過來了.
注意：當(dāng)從數(shù)據(jù)庫有大量的查詢時,可以暫時將從數(shù)據(jù)庫的復(fù)制線程關(guān)閉掉,等查詢量降下來了,再打開,這樣也不會丟失數(shù)據(jù).

MYSQL數(shù)據(jù)庫附：一個優(yōu)化好后的主數(shù)據(jù)庫設(shè)置裝備擺設(shè)文件和從數(shù)據(jù)設(shè)置裝備擺設(shè)文件內(nèi)容如下：

代碼如下:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
?
[mysqld]
sync_binlog=1
server-id=1
port=3306
socket=/usr/local/mysql/mysql.sock
pid-file=/home/mysql/temp/my3306.pid
user=mysql
datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
log-bin=/home/mysql/data/mysqlmaster-bin
log-error=/home/mysql/logs/error.log
slow_query_log_file=/home/mysql/logs/slow.log
binlog_format=mixed
slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
character_set_server=utf8
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
expire-logs-days=10
skip-slave-start
skip-name-resolve
lower_case_table_names=1
log_bin_trust_function_creators=1
?
# InnoDB
innodb_data_home_dir=/home/mysql/data
innodb_log_group_home_dir=/home/mysql/logs
innodb_data_file_path=ibdata1:128M:autoextend
innodb_buffer_pool_size=2G
innodb_log_file_size=10M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_flush_log_at_trx_commit=1
?
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
?

?
?
一個優(yōu)化好的從數(shù)據(jù)庫的設(shè)置裝備擺設(shè)文件如下：
代碼如下:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
?
[mysqld]
sync_binlog=1
server-id=2
port=3306
socket=/usr/local/mysql/mysql.sock
pid-file=/home/mysql/temp/my3306.pid
user=mysql
datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
log-bin=/home/mysql/data/mysqlslave-bin
log-error=/home/mysql/logs/error.log
slow_query_log_file=/home/mysql/logs/slow.log
binlog_format=mixed
slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
character_set_server=utf8
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
expire-logs-days=10
#skip-slave-start
skip-name-resolve
lower_case_table_names=1
log_bin_trust_function_creators=1
?
# InnoDB
innodb_data_home_dir=/home/mysql/data
innodb_log_group_home_dir=/home/mysql/logs
innodb_data_file_path=ibdata1:128M:autoextend
innodb_buffer_pool_size=2G
innodb_log_file_size=10M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_flush_log_at_trx_commit=1
?
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
?
[mysqldump]
quick
max_allowed_packet = 16M
?
[mysql]
no-auto-rehash
?
[myisamchk]
key_buffer_size = 256K
sort_buffer_size = 256K
read_buffer = 256K
write_buffer = 256K
?
[mysqlhotcopy]
interactive-timeout
?
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
?
[mysqldump]
quick
max_allowed_packet = 16M
?
[mysql]
no-auto-rehash
?
[myisamchk]
key_buffer_size = 256K
sort_buffer_size = 256K
read_buffer = 256K
write_buffer = 256K
?
[mysqlhotcopy]
interactive-timeout

《MYSQL數(shù)據(jù)庫MySQL5.6 Replication主從復(fù)制（讀寫分離) 配置完整版》是否對您有啟發(fā)，歡迎查看更多與《MYSQL數(shù)據(jù)庫MySQL5.6 Replication主從復(fù)制（讀寫分離) 配置完整版》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/12647.html


    標(biāo)簽：