Centos下mysql主從配置

原理

見參考 1 和 參考2

環(huán)境配置

主機(jī) 描述
192.168.231.149 master
192.168.231.151 slave-2

配置流程

master節(jié)點(diǎn)

  1. 配置master節(jié)點(diǎn)的配置文件
  2. 創(chuàng)建專門用來進(jìn)行主從復(fù)制的賬號(hào)
  3. 鎖定數(shù)據(jù)庫，并且將當(dāng)前master中的數(shù)據(jù)導(dǎo)出，并且記錄當(dāng)前的binlog的文件名稱和位置

slave節(jié)點(diǎn)

  1. 配置slave節(jié)點(diǎn)的配置文件
  2. 導(dǎo)入master數(shù)據(jù)庫中的文件
  3. 使用change master to命令使其成為slave節(jié)點(diǎn)

注意：先嘗試在slave節(jié)點(diǎn)上連接master節(jié)點(diǎn)的mysql-server，提前查看是否有防火墻等問題

mysql的安裝

1. 下載相應(yīng)的軟件源安裝包，并且生成yum
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
[root@localhost ~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm # 生成mysql的yum源
[root@localhost ~]# ls /etc/yum.repos.d/  # 查看是否有mysql的yum源
CentOS-Base.repo  CentOS-CR.repo  CentOS-Debuginfo.repo  CentOS-fasttrack.repo  CentOS-Media.repo  CentOS-Sources.repo  CentOS-Vault.repo  mysql-community.repo  mysql-community-source.repo
2. 下載mysql-server
[root@localhost ~]# yum search mysql  # 查找mysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.lzu.edu.cn
 * extras: mirrors.cqu.edu.cn
 * updates: mirrors.cqu.edu.cn
============================================================================================================== N/S matched: mysql ===============================================================================================================
MySQL-python.x86_64 : An interface to MySQL
akonadi-mysql.x86_64 : Akonadi MySQL backend support
apr-util-mysql.x86_64 : APR utility library MySQL DBD driver
dovecot-mysql.x86_64 : MySQL back end for dovecot
freeradius-mysql.x86_64 : MySQL support for freeradius
libdbi-dbd-mysql.x86_64 : MySQL plugin for libdbi
mysql-community-client.i686 : MySQL database client applications and tools
....
mysql-community-server.x86_64
...
[root@localhost ~]# yum install mysql-community-server.x86_64
...
Installed:
  mysql-community-libs.x86_64 0:5.7.26-1.el7                                   mysql-community-libs-compat.x86_64 0:5.7.26-1.el7                                   mysql-community-server.x86_64 0:5.7.26-1.el7                                  

Dependency Installed:
  mysql-community-client.x86_64 0:5.7.26-1.el7     mysql-community-common.x86_64 0:5.7.26-1.el7     net-tools.x86_64 0:2.0-0.24.20131004git.el7      perl.x86_64 4:5.16.3-294.el7_6               perl-Carp.noarch 0:1.26-244.el7            
  perl-Encode.x86_64 0:2.51-7.el7                  perl-Exporter.noarch 0:5.68-3.el7                perl-File-Path.noarch 0:2.09-2.el7               perl-File-Temp.noarch 0:0.23.01-3.el7        perl-Filter.x86_64 0:1.49-3.el7            
  perl-Getopt-Long.noarch 0:2.40-3.el7             perl-HTTP-Tiny.noarch 0:0.033-3.el7              perl-PathTools.x86_64 0:3.40-5.el7               perl-Pod-Escapes.noarch 1:1.04-294.el7_6     perl-Pod-Perldoc.noarch 0:3.20-4.el7       
  perl-Pod-Simple.noarch 1:3.28-4.el7              perl-Pod-Usage.noarch 0:1.63-3.el7               perl-Scalar-List-Utils.x86_64 0:1.27-248.el7     perl-Socket.x86_64 0:2.010-4.el7             perl-Storable.x86_64 0:2.45-3.el7          
  perl-Text-ParseWords.noarch 0:3.29-4.el7         perl-Time-HiRes.x86_64 4:1.9725-3.el7            perl-Time-Local.noarch 0:1.2300-2.el7            perl-constant.noarch 0:1.27-2.el7            perl-libs.x86_64 4:5.16.3-294.el7_6        
  perl-macros.x86_64 4:5.16.3-294.el7_6            perl-parent.noarch 1:0.225-244.el7               perl-podlators.noarch 0:2.5.1-3.el7              perl-threads.x86_64 0:1.87-4.el7             perl-threads-shared.x86_64 0:1.43-6.el7    

Replaced:
  mariadb-libs.x86_64 1:5.5.60-1.el7_5                                                                                                                                                                                                           

Complete!

配置master節(jié)點(diǎn)

[root@localhost ~]# systemctl start mysqld  # 開啟mysql服務(wù)
[root@localhost ~]# systemctl enable mysqld  # 開機(jī)就啟動(dòng)mysql服務(wù)
[root@localhost ~]# cat /etc/my.cnf    # 查看mysql的的配置文件，其中mysql的日志文件中存放著root用戶的初始密碼
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# 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

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@localhost ~]# cat /var/log/mysqld.log | grep password   # 查看日志文件為root用戶初始化的密碼
2019-06-30T13:08:24.277971Z 1 [Note] A temporary password is generated for root@localhost: my<:IhMf*8hb
[root@localhost ~]# mysql -u root -p  # 根據(jù)上面的密碼登陸mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ABCabc123...';  # 修改root的密碼
# 模擬一點(diǎn)mysql的數(shù)據(jù)出來
mysql> create database user;
Query OK, 1 row affected (0.00 sec)
mysql> use user;
Database changed
mysql> create table user(name char(20), age int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user(name, age) values ('allen', 15);
Query OK, 1 row affected (0.08 sec)
mysql> insert into user(name, age) values ('ketty', 18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-------+------+
| name  | age  |
+-------+------+
| allen |   15 |
| ketty |   18 |
+-------+------+
2 rows in set (0.00 sec)

# 創(chuàng)建一個(gè)專門用來進(jìn)行主從復(fù)制的用戶，并且授權(quán)
mysql> CREATE USER 'slave2'@'192.168.231.151' IDENTIFIED BY 'ABCabc123...';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.231.151';
mysql> FLUSH  PRIVILEGES;
mysql> select user, host from mysql.user;
+---------------+-----------------+
| user          | host            |
+---------------+-----------------+
| slave2        | 192.168.231.151 |
| mysql.session | localhost       |
| mysql.sys     | localhost       |
| root          | localhost       |
+---------------+-----------------+
5 rows in set (0.00 sec)
# 查看一下未配置的master的狀態(tài)
mysql> show master status;
Empty set (0.00 sec)
# 配置mysql的配置文件，修改如下兩項(xiàng)
[root@localhost ~]# vi/etc/my.cnf
log_bin=mysql-bin
server_id=1
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;   
# 打開另外一個(gè)終端，導(dǎo)出數(shù)據(jù)庫中數(shù)據(jù)
[root@localhost ~]# mysqldump -u root -p --all-databases --master-data > dbdump.db

slave節(jié)點(diǎn)的配置

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
# 測(cè)試遠(yuǎn)程連接一下master
[root@localhost ~]# mysql -h 192.168.231.149 -P 3306 -u slave2 -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.231.149' (113)
# 在master節(jié)點(diǎn)上輸入iptables -F即可以解決
# 修改配置文件并且修改root賬號(hào)的密碼
[root@localhost ~]# vi /etc/my.cnf
server_id=3
read_only=1
super_read-only=1
# 一般slave節(jié)點(diǎn)需要設(shè)置為只讀權(quán)限，由需要根據(jù)不同的用戶設(shè)置不同的只讀權(quán)限，詳情細(xì)節(jié)見參考3
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# cat /var/log/mysqld.log  | grep password
2019-07-01T02:13:57.213304Z 1 [Note] A temporary password is generated for root@localhost: d#Ar&E9-/>z.
[root@localhost ~]# mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ABCabc123...';
# 導(dǎo)入來自于master節(jié)點(diǎn)的數(shù)據(jù)
[root@localhost ~]# mysql -u root -p < dbdump.db
# 將該節(jié)點(diǎn)變成slave節(jié)點(diǎn)
[root@localhost ~]# mysql -u root -p
mysql> show slave status;
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
     MASTER_HOST='192.168.231.151',
     MASTER_USER='slave2',
     MASTER_PASSWORD='ABCabc123...',
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status \G;
...
             Slave_IO_Running: Yes  # 這兩個(gè)字段為yes表示成功
            Slave_SQL_Running: Yes
...

測(cè)試

1 在master節(jié)點(diǎn)
mysql> unlock tables;   # 解除對(duì)表的鎖定
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table company(name char(20), money int);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into company(name, money) values ('google', 20000);
Query OK, 1 row affected (0.20 sec)
mysql> insert into company(name, money) values ('facebook', 20000);
Query OK, 1 row affected (0.03 sec)
2 在slave節(jié)點(diǎn)上查看數(shù)據(jù)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| user               |
+--------------------+
6 rows in set (0.01 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> select * from company;
+----------+-------+
| name     | money |
+----------+-------+
| google   | 20000 |
| facebook | 20000 |
+----------+-------+
2 rows in set (0.00 sec)

(喜歡的朋友，記得點(diǎn)個(gè)贊，有疑問請(qǐng)?jiān)谙路皆u(píng)論)

參考

1 http://www.itdecent.cn/p/b0cf461451fb

2 https://dev.mysql.com/doc/refman/5.7/en/replication.html

3 https://www.cnblogs.com/qlqwjy/p/8541959.html

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 昨天陪外婆打麻將，不經(jīng)意間比較了3人的手臂，竟然發(fā)現(xiàn)我是最-黑-的!嚇了一跳。我腫么就成了現(xiàn)在這個(gè)小黑人？從小到大...
    Vera_yuan閱讀 513評(píng)論 0贊 0
  • 心靈的寬度，不是你認(rèn)識(shí)了多少人，而是你包容了多少人。 做人如山，望萬物，而容萬物。做人似水，能進(jìn)退...
    白格yf閱讀 98評(píng)論 0贊 1
  • 曙光健身學(xué)院2.14日分享 年初十，開春工作日開始啦?????????? 你制定新春目標(biāo)了嗎???? 這預(yù)示著你新的一...
    哈嘍喋喋閱讀 188評(píng)論 0贊 0
  • 2018年1月25號(hào)我來到了你走過的路
    我喜歡你久了閱讀 192評(píng)論 0贊 0
  • session session與cookie類似，都是一種緩存機(jī)制，不同的是cookie存儲(chǔ)在服務(wù)器中，sessi...
    猛犸象和劍齒虎閱讀 879評(píng)論 0贊 1

友情鏈接更多精彩內(nèi)容