LINUX——關於mysql中較詳細的，資料庫的主從配置，自動備份以及讀寫分離的使用

標籤：企業   creat   操作記錄   column   sha   can   nlog   blog   修改   

1.主從簡介
資料在企業中是非常重要的一部分，儲存資料的資料庫多種多用樣，但卻都存在著一種隱患
·資料庫資料丟失
·資料庫訪問人數較多時，一台資料庫無法保證服務品質
2.主從的作用
·即時災備，用於故障切換
·讀寫分離，提供查詢服務
·備份，避免影響業務
3.主從形式
·一主一叢
·主主複製
·一主多從
·多主一叢
·聯級複製

主從複製原理

主從複製步驟

主庫將所有寫的操作記錄到binlog日誌(二進位日誌)中並產生一個log dump線程，將binlog日誌傳給從庫的I/O線程

從庫產生兩個線程，一個I/O線程，一個SQL線程·I/O線程去請求主庫的binlog，並將得到的binlog日誌寫到relay log（中繼日誌）檔案中·SQl線程，會讀取relay log 檔案中的日誌，並解析成具體操作，來實現主從的操作一致，達到最終資料一致的目的

主從複製配置步驟：
1.確保從資料庫與主要資料庫裡的資料一樣
2.在主要資料庫建立一個同步帳號授權給從資料庫使用
3.配置主要資料庫(修改設定檔)
4.配置從資料庫(修改設定檔)

配置需求：
環境介紹：搭建兩台mysql伺服器，一台作為主伺服器，一台作為從伺服器，主伺服器進行寫操作，從伺服器進行讀操作

配置資訊
主要資料庫 ip192.168.56.11 centos7 有資料
從資料庫 ip192.168.56.138 centos7 無資料

主從同步設定
防火牆設定

[[email protected] ~]# systemctl stop firewalld[[email protected] ~]# systemctl disable firewalld[[email protected] ~]# sed -ri ‘s/(SELINUX=).*/\1disabled/g‘ /etc/selinux/config[[email protected] ~]# setenforce 0

下載二進位格式的mysql包

[[email protected] ~]# cd /usr/src/[[email protected] src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz[[email protected] src]# lsdebug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

建立主和組

[[email protected] src]# groupadd -r mysql[[email protected] src]# useradd -M -s /sbin/nologin -g mysql mysql[[email protected] src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[[email protected] src]# cd /usr/local/[[email protected] local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"

//修改目錄/usr/local/mysql的屬主屬組

[[email protected] local]# chown -R mysql.mysql /usr/local/mysql[[email protected] local]# ll /usr/local/mysql -dlrwxrwxrwx. 1 mysql mysql 36 9月   7 11:43 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

//添加環境變數

[[email protected] local]# ls /usr/local/mysqlbin  COPYING  docs  include  lib  man  README  share  support-files[[email protected] local]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh[[email protected] local]# . /etc/profile.d/mysql.sh[[email protected] local]# echo $PATH/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立資料存放目錄

[[email protected] local]# mkdir /opt/data[[email protected] local]# chown -R mysql.mysql /opt/data/[[email protected] local]# ll /opt/總用量 0drwxr-xr-x. 2 mysql mysql 6 9月   7 11:47 datadrwxr-xr-x. 2 root  root  6 3月  26 2015 rh

//初始化資料庫

[[email protected] local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/2018-09-07T03:48:13.790964Z 1 [Note] A temporary password is generated for [email protected]: n*Fsa&a+O1kN  /最後會產生一個臨時密碼

//從：資料庫初始化，前面步驟一樣，所以省略

[[email protected] local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/2018-09-07T06:25:11.123940Z 1 [Note] A temporary password is generated for [email protected]: QR(XhNPpk6Qi

//組建組態檔案/

[[email protected] ~]# cat > /etc/my.cnf <<EOF[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveEOF

//佈建服務啟動指令碼[[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g‘ /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(datadir=).*#\1/opt/data#g‘ /etc/init.d/mysqld

//啟動mysql

[[email protected] ~]# /etc/init.d/mysqld start[[email protected] ~]# ps -ef|grep mysql[[email protected] ~]# ss -antlLISTEN 0 80 :::3306 :::*

//修改密碼
//使用臨時密碼登陸

[[email protected]st ~]# /usr/local/mysql/bin/mysql -uroot -pEnter password:mysql>//設定新密碼mysql> set password = password(‘guohui123‘);以上是兩台資料庫伺服器的配置，配置方式相同

主：//查看主從庫的備份

[[email protected] local]# mysql -uroot -pguohui123 -e ‘show databases;‘mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |建立一個庫，表mysql> use guohuiDatabase changedmysql> create table student (id int(11) NOT NULL,name varchar(100) NOT NULL,age TINYINT(3));Query OK, 0 rows affected (0.02 sec)mysql> insert into student (id,name,age) value (1,‘tom‘,20);Query OK, 1 row affected (0.05 sec)mysql> desc student;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int(11)      | NO   |     | NULL    |       || name  | varchar(100) | NO   |     | NULL    |       || age   | tinyint(3)   | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> select * from student;+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   20 |+----+------+------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || guohui             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

主：//全備份主庫
全備主庫時需要另外開一個終端，給資料加上讀鎖，避免在備份期間有其他人在寫入資料導致資料不一致，並且鎖必須在備份完成後才能退出，退出則解鎖

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

主：//備份主庫並將檔案傳輸到從庫

[[email protected] ~]# mysqldump -uroot -pguohui123 --all-databases > /opt/all-20180907-1.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# ls /optall-20180907-1.sql  data  rh//傳輸[[email protected] ~]# scp /opt/all-20180907-1.sql [email protected]:/opt/The authenticity of host ‘192.168.56.138 (192.168.56.138)‘ can‘t be established.ECDSA key fingerprint is SHA256:byE1DjsPUpqjJssSP+RcbkM7+wcjdzMkjW3u09KrSB8.ECDSA key fingerprint is MD5:c6:d2:a1:34:73:2f:66:c9:ac:db:ca:2b:7a:78:4f:75.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added ‘192.168.56.138‘ (ECDSA) to the list of known hosts.[email protected]‘s password:1    //這裡輸入root密碼all-20180907-1.sql                                                  100%  783KB  19.2MB/s   00:00  主：//接觸鎖表狀態mysql> quitBye

從：//在從庫上恢複主庫的備份並查看有哪些庫，確保與主庫一致

//查看主備傳過來的檔案[[email protected] ~]# ls /optall-20180907-1.sql  data  rh將檔案匯入從資料庫[[email protected] ~]# mysql -uroot -pguohui123 < /opt/all-20180907-1.sqlmysql: [Warning] Using a password on the command line interface can be insecure.//查看資料庫[[email protected] ~]# mysql -uroot -pguohui123 -e ‘show databases;‘mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || guohui             || mysql              || performance_schema || sys                |+--------------------+

主：//在主要資料庫裡建立一個同步帳號授權給從資料庫使用

建立資料庫使用者guohui，密碼：guohui123，只能在192.168.56.138上遠程登陸mysql> CREATE USER ‘guohui‘@‘192.168.56.138‘ IDENTIFIED BY ‘guohui123‘;Query OK, 0 rows affected (0.00 sec)賦予許可權使用者guohui，可以在192.168.56.138上訪問本機所有資料庫的所有表，無需密碼：mysql> GRANT REPLICATION SLAVE ON *.* TO ‘guohui‘@‘192.168.56.138‘;Query OK, 0 rows affected (0.00 sec)重新載入特權：mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql>

主：//配置主要資料庫

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.pidlog-error = /opt/data/mysqld.log   //錯誤記錄檔user = mysqlskip-name-resolve#log-bin = mysql_bin   //啟動binlog日誌server-id = 2   //資料庫伺服器唯一識別碼，主庫的server-id值必須比從庫的大啟動[[email protected] ~]# /etc/init.d/mysqld restartERROR! MySQL server PID file could not be found!Starting MySQL.Logging to ‘/opt/data/mysqld.log‘.. SUCCESS!查看主庫狀態：mysql> show master status;mysql_bin，檔案裡寫的二進位檔案名字000001  版本Position  位置 154+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql_bin.000001 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

從：//配置從資料庫

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.pidlog-error = /opt/data/mysql.loguser = mysqlskip-name-resolve#replicationserver-id = 5  //設定從庫的唯一識別碼，從庫的server-id值必須小於主庫的該值relay-log = mysql_relay_log    //啟動中繼日誌relay-log啟動[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL.Logging to ‘/opt/data/mysql.log‘.SUCCESS!

從：//配置並啟動主從複製

CHANGE MASTER TO                       指定的主伺服器MASTER_HOST=‘192.168.56.11‘,     指定的主伺服器ipMASTER_USER=‘guohui‘,MASTER_PASSWORD=‘guohui123‘,  密碼MASTER_LOG_FILE=‘mysql-bin.000001‘,     二進位檔案的名字與版本MASTER_LOG_POS=154;                   位置 154mysql> change master to master_host=‘192.168.56.11‘,master_user=‘guohui‘,master_password=‘guohui123‘,master_log_file=‘mysql_bin.000001‘,master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.02 sec)啟動從服務的從服務mysql> start slave;Query OK, 0 rows affected (0.00 sec)

從：//查看從伺服器狀態

mysql> show slave status \G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.56.11                  Master_User: guohui                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql_bin.000002          Read_Master_Log_Pos: 154               Relay_Log_File: mysql_relay_log.000004                Relay_Log_Pos: 367        Relay_Master_Log_File: mysql_bin.000002             Slave_IO_Running: Yes       //必須yes            Slave_SQL_Running: Yes     //必須yesmysql>

測試：
主：//在資料庫中插入資料

mysql> select * from student;+----+------+------+| id | name | age  |+----+------+------+|  1 | tom  |   20 |+----+------+------+1 row in set (0.00 sec)mysql> insert into student value (1,‘jerry‘,23);Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | tom   |   20 ||  1 | jerry |   23 |+----+-------+------+2 rows in set (0.00 sec)mysql>

從：//查看測試結果

mysql> use guohui;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from student;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | tom   |   20 ||  1 | jerry |   23 |+----+-------+------+2 rows in set (0.00 sec)mysql>

由此可見，資料庫的主從功能，搭建成功

LINUX——關於mysql中較詳細的，資料庫的主從配置，自動備份以及讀寫分離的使用