Linux系統營運之MYSQL資料庫叢集部署(主從複製)

標籤：comm   create   flush   tmp   安裝mysql   環境變數   put   init.d   sed   

一、介紹

　　　　Mysql主從複製，前段時間生產環境部署了一套主從複製的架構，當時現找了很多資料，現在記錄下

二、拓撲圖

三、環境以及軟體版本

主機名稱	IP	作業系統	角色	軟體版本
MysqlDB_Master	192.168.0.1	CentOS release 7.1	Master	Mysql 5.6.36
MysqlDB_Slave	192.168.0.2	CentOS release 7.1	Slave	Mysql 5.6.36

四、源碼安裝

　　　　之前都是yum直接安裝的，這次嘗試使用源碼安裝，安裝包如下：

• mysql-5.6.36.tar.gz
• cmake-3.8.1.tar.gz

　　　　首先安裝前提環境：

[[email protected]_Master soft] yum  groupinstall ‘Development tools‘ -y[[email protected]_Master soft] tar -xf cmake-3.8.1.tar.gz[[email protected]_Master soft] cd cmake-3.8.1[[email protected]_Master cmake-3.8.1] ./bootstrap[[email protected]_Master cmake-3.8.1] gmake && gmke install

 　　　　安裝mysql：

[[email protected]_Master soft] tar -xf mysql-5.6.36.tar.gz[[email protected]_Master soft] cd  mysql-5.6.36[[email protected]_Master mysql-5.6.36] cmake -DCMAKE_INSTALL_PREFIX=/data/opt/mysql -DMYSQL_DATADIR=/data/opt/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1[[email protected]_Master mysql-5.6.36] make && make install

　　　　mysql配置相關：

[[email protected]_Master mysql-5.6.36] mkdir -p /data/opt/mysql/data/{3306,binlog,tmp}[[email protected]_Master mysql-5.6.36] chown -R mysql.mysql /data/opt/mysql[[email protected]_Master mysql-5.6.36] cp support-files/my-default.cnf /etc/my.conf[[email protected]_Master mysql-5.6.36] vim /etc/my.conf===================================[client]port = 3306socket = /data/opt/mysql/3306/mysql.sock#The MySQL Server[mysqld]server-id=209innodb_flush_log_at_trx_commit=1sync_binlog=1port = 3306user = mysqlsocket = /data/opt/mysql/3306/mysql.sockpid-file = /data/opt/mysql/3306/mysql.pidbasedir = /data/opt/mysqldatadir = /data/opt/mysql/datatmpdir = /data/opt/mysql/tmpopen_files_limit = 10240lower_case_table_names=1explicit_defaults_for_timestampsql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#Buffermax_allowed_packet = 256Mmax_heap_table_size = 256Mnet_buffer_length = 8ksort_buffer_size = 2Mjoin_buffer_size = 4Mread_buffer_size = 2Mread_rnd_buffer_size = 16M#Loglog-bin = /data/opt/mysql/binlog/mysql-binbinlog_cache_size = 32Mmax_binlog_cache_size = 512Mmax_binlog_size = 512Mbinlog_format = mixedlog_output = FILElog-error = /data/opt/mysql/binlog/mysql-error.logslow_query_log = 1slow_query_log_file = /data/opt/mysql/binlog/slow_query.loggeneral_log = 0general_log_file = /data/opt/mysql/binlog/general_query.logexpire-logs-days = 14#InnoDBinnodb_data_file_path = ibdata1:2048M:autoextendinnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_buffer_pool_size = 1024M[mysql]no-auto-rehashprompt = (\[email protected]\h)[\d]>\_default-character-set = gbk===================================#添加mysql環境變數[[email protected]_Master mysql-5.6.36] echo ‘export PATH=$PATH:/data/opt/mysql/bin‘  >> /etc/profile && source /etc/profile#初始化資料庫[[email protected]_Master mysql-5.6.36] cd /data/opt/mysql[[email protected]_Master mysql] ./scripts/mysql_install_db --defults-file=/etc/my.cnf --user=mysql[[email protected]_Master mysql] ./bin/mysqld_safe --user=mysql &#添加service啟動[[email protected]_Master mysql] cp /soft/mysql-5.6.36/support-files/mysql.server /etc/init.d/mysqld[[email protected]_Master mysql] chmod +x /ect/init.d/mysqld

　　　　最佳化mysql預設配置，配置存取權限：

[[email protected]_Master ~] mysql -uroot -p#清空預設資料([email protected])[(none)] > select * from mysql.db \G([email protected])[(none)] > truncate table mysql.db;([email protected])[(none)] > flush privileges;([email protected])[(none)] > select * from mysql.db \G#配置存取權限([email protected])[(none)] > use mysql;([email protected])[(none)] > desc user;([email protected])[(none)] > grant all privileges on *.* to root@"%" identified by "root";([email protected])[(none)] > update user set Password=password(‘XXXXXX‘) where User=‘root‘;([email protected])[(none)] > select Host,User,Password from user where User=‘root‘;([email protected])[(none)] > flush privileges;([email protected])[(none)] > exit;

　　　　以上，Mysql源碼安裝完成，在MysqlDB_Slave伺服器上進行相同配置即可。

 五、配置主從

　　　　關於主從的配置，之前在設定檔裡已經填寫，“server-id”值主從不同，從值大於主值，主要配置如下：

[mysqld]server-id=209#InnoDBinnodb_flush_log_at_trx_commit=1sync_binlog=1

　　　　串連到主庫，配置同步帳號並授權從伺服器串連：

([email protected])[(none)] grant replication slave,reload,super on *.* to ‘repl‘@‘192.168.0.2‘ identified by ‘backup‘;([email protected])[(none)] flush tables with read lock;([email protected])[(none)] show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000007 |       321|              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

　　　　串連到從庫，配置主伺服器IP以及同步帳號等資訊：

([email protected])[(none)] > change master to master_host=‘192.168.0.1‘,master_user=‘repl‘,master_password=‘backup‘,master_log_file=‘mysql-bin.000007‘,master_log_pos=321;([email protected])[(none)] > start slave;([email protected])[(none)] > show slave status \G

　　　　確認主從節點同步正常，主要確認以下值：

Master_Log_File: mysql-bin.000007Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: Yes

　　　　串連到主庫，解除鎖，並建立新DB：

([email protected])[(none)] unlock tables;([email protected])[(none)] create database testDB;

　　　　串連到從庫，查看建立DB是否同步過來：

([email protected])[(none)]  show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || testDB             |+--------------------+

　　　　以上，Mysql叢集，主從同步配置完成。

　　　　

Linux系統營運之MYSQL資料庫叢集部署(主從複製)