Mysql中Master-slave如何配置

小編給大家分享一下MySQL中Master-slave如何配置，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

專業(yè)成都網(wǎng)站建設(shè)公司，做排名好的好網(wǎng)站，排在同行前面，為您帶來客戶和效益!創(chuàng)新互聯(lián)為您提供成都網(wǎng)站建設(shè)，五站合一網(wǎng)站設(shè)計(jì)制作，服務(wù)好的網(wǎng)站設(shè)計(jì)公司，網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作負(fù)責(zé)任的成都網(wǎng)站制作公司!

搭建了一下mysql master slave的環(huán)境
在此做一下簡單記錄
mysql數(shù)據(jù)庫版本:5.7-18

master與slave均采用了如下方式初始化mysql數(shù)據(jù)庫

mkdir -p /data/mysql
useradd mysql
chown -R mysql:mysql /data/
chown -R mysql:mysql /usr/local/mysql*


/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/ --user=mysql --initialize-insecure


Master的my.cnf配置：

  • [client]

  • port = 3306

  • socket = /tmp/mysql.sock

  • #default-character-set=utf8

  • [mysql]

  • #default-character-set=utf8

  • [mysqld]

  • port = 3306

  • socket = /tmp/mysql.sock

  • basedir = /usr/local/mysql

  • datadir = /data/mysql

  • server_id=151

  • open_files_limit = 3072

  • back_log = 103

  • max_connections = 512

  • max_connect_errors = 100000

  • table_open_cache = 512

  • external-locking = FALSE

  • max_allowed_packet = 128M

  • sort_buffer_size = 2M

  • join_buffer_size = 2M

  • thread_cache_size = 51

  • query_cache_size = 32M

  • tmp_table_size = 96M

  • max_heap_table_size = 96M

  • slow_query_log = 1

  • slow_query_log_file = /data/mysql/slow.log

  • log-error = /data/mysql/error.log

  • long_query_time = 0.05

  • log-bin = /data/mysql/mysql-bin

  • sync_binlog = 1

  • binlog_cache_size = 4M

  • max_binlog_cache_size = 128M

  • max_binlog_size = 1024M

  • expire_logs_days = 7

  • key_buffer_size = 32M

  • read_buffer_size = 1M

  • read_rnd_buffer_size = 16M

  • bulk_insert_buffer_size = 64M

  • character-set-server=utf8

  • default-storage-engine=InnoDB

  • binlog_format=row

  • #gtid_mode=on

  • #log_slave_updates=1

  • #enforce_gtid_consistency=1

  • interactive_timeout=100

  • wait_timeout=100

  • transaction_isolation = REPEATABLE-READ

  • #innodb_additional_mem_pool_size = 16M

  • innodb_buffer_pool_size = 1434M

  • innodb_data_file_path = ibdata1:1024M:autoextend

  • innodb_flush_log_at_trx_commit = 1

  • innodb_log_buffer_size = 16M

  • innodb_log_file_size = 256M

  • innodb_log_files_in_group = 2

  • innodb_max_dirty_pages_pct = 50

  • innodb_file_per_table = 1

  • innodb_locks_unsafe_for_binlog = 0

  • [mysqldump]

  • quick

  • max_allowed_packet = 32M

Slave的配置文件：/etc/my.cnf

  • [client]

  • port = 3306

  • socket = /tmp/mysql.sock

  • #default-character-set=utf8

  • [mysql]

  • #default-character-set=utf8

  • [mysqld]

  • port = 3306

  • socket = /tmp/mysql.sock

  • basedir = /usr/local/mysql

  • datadir = /data/mysql

  • server_id=152

  • #master slave replicat

  • #master-host=192.168.43.151

  • #master-user=repl

  • #master-password=repl

  • relay-log=/data/mysql/mysql-replay-bin

  • master-info-file = /data/mysql/mysql-master.info

  • relay-log-info-file = /data/mysql/mysql-relay-log.info

  • open_files_limit = 3072

  • back_log = 103

  • max_connections = 512

  • max_connect_errors = 100000

  • table_open_cache = 512

  • external-locking = FALSE

  • max_allowed_packet = 128M

  • sort_buffer_size = 2M

  • join_buffer_size = 2M

  • thread_cache_size = 51

  • query_cache_size = 32M

  • tmp_table_size = 96M

  • max_heap_table_size = 96M

  • slow_query_log = 1

  • slow_query_log_file = /data/mysql/slow.log

  • log-error = /data/mysql/error.log

  • long_query_time = 0.05

  • log-bin = /data/mysql/mysql-bin

  • sync_binlog = 1

  • binlog_cache_size = 4M

  • max_binlog_cache_size = 128M

  • max_binlog_size = 1024M

  • expire_logs_days = 7

  • key_buffer_size = 32M

  • read_buffer_size = 1M

  • read_rnd_buffer_size = 16M

  • bulk_insert_buffer_size = 64M

  • character-set-server=utf8

  • default-storage-engine=InnoDB

  • binlog_format=row

  • #gtid_mode=on

  • #log_slave_updates=1

  • #enforce_gtid_consistency=1

  • interactive_timeout=100

  • wait_timeout=100

  • transaction_isolation = REPEATABLE-READ

  • #innodb_additional_mem_pool_size = 16M

  • innodb_buffer_pool_size = 1434M

  • innodb_data_file_path = ibdata1:1024M:autoextend

  • innodb_flush_log_at_trx_commit = 1

  • innodb_log_buffer_size = 16M

  • innodb_log_file_size = 256M

  • innodb_log_files_in_group = 2

  • innodb_max_dirty_pages_pct = 50

  • innodb_file_per_table = 1

  • innodb_locks_unsafe_for_binlog = 0

  • [mysqldump]

  • quick

  • max_allowed_packet = 32M

注意：master-host這個(gè)參數(shù)5.7已經(jīng)不支持。
參考：
https://blog.csdn.net/edwzhang/article/details/8819629


初始話完成之后，在master通過mysqldump導(dǎo)出mysql數(shù)據(jù)庫
會(huì)話1：
[root@mysql01 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)


Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.05 sec)


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)


mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      581 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


這個(gè)Master Status很重要，在其后的slave配置中需要依賴它


mysqldump -u root -p --all-databases --master-data > /root/dbdump.db

導(dǎo)出之后，釋放鎖

mysql> unlock tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

slave端導(dǎo)入， 導(dǎo)入之后重啟mysql服務(wù)
mysql -u root -p < /root/dbdump.db

在slave端啟用復(fù)制：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.151',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000017',
    -> MASTER_LOG_POS=581;
Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

在master端進(jìn)行測(cè)試:

mysql> create database test2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Query OK, 1 row affected (0.28 sec)

mysql>
mysql> show slave staus
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'staus' at line 1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      743 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>
mysql> use test02
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

ERROR 1049 (42000): Unknown database 'test02'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test2
Database changed
mysql>
mysql>
mysql> create table mytest01(pid int, nme varchar(100));
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql> insert into mytest01 values(1, 'AAAA');
Query OK, 1 row affected (0.04 sec)

mysql>

如果配置正確，應(yīng)當(dāng)可以在slave端看到數(shù)據(jù)

以上是“Mysql中Master-slave如何配置”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享名稱：Mysql中Master-slave如何配置
鏈接分享：http://muchs.cn/article48/pdjeep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、電子商務(wù)、動(dòng)態(tài)網(wǎng)站、自適應(yīng)網(wǎng)站、、域名注冊(cè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

搜索引擎優(yōu)化