Mysql高可用高性能存儲應用系列3 - mysqld_multi配置主從集群_環(huán)球熱頭條

• 2023-03-31 13:31:15來源：騰訊云

概述

主從復制要解決的問題，1)寫操作鎖表，影響讀操作，影響業(yè)務。2)數(shù)據(jù)庫備份。3)隨著數(shù)據(jù)增加，I/O操作增多，單機出現(xiàn)瓶頸。

【資料圖】

主從復制就是從服務器的主節(jié)點，復制到多個從節(jié)點，默認采用異步的方式。

主從復制原理

從庫服務器會開啟兩個一部的獨立線程，I/O線程和Sql進程。

I/O線程 : 負責連接master連接成功后，睡眠并等待master產(chǎn)生新的事件，有新的就保存到自己的中繼日志中，中繼日志通常位于操作系統(tǒng)的緩存中，所以開銷很小。Sql進程：負責執(zhí)行中繼日志中的sql操作。

源碼安裝Mysql

使用cmake安裝mysql-5.7.37，Linux系統(tǒng)是centos7，話不多說，我們開始吧，安裝之前的準備工作特別重要。

1.卸載系統(tǒng)自帶mysql

# rpm -qa | grep mysql# rpm -qa | grep mariadbmariadb-libs-5.5.60-1.el7_5.x86_64# rpm -e --nodeps mariadb*# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64

復制

2.添加mysql禁止登錄的用戶及目錄規(guī)劃

useradd -M -s /sbin/nologin mysql

復制

# 軟件目錄(為mysql安裝目錄創(chuàng)建軟鏈接或改目錄名)# ln -s  /usr/local/mysql-5.7.37 /usr/local/mysql# 數(shù)據(jù)目錄# mkdir /usr/local/mysql-5.7.37/data# 日志目錄# mkdir /var/log/mysql# touch /var/log/mysql/mysqld.log# chown -R mysql.mysql /var/log/mysql/mysqld.log# 其他目錄（）# mkdir /usr/local/mysql-5.7.37/tmp# chown -R mysql.mysql /usr/local/mysql-5.7.37

復制

3.下載、解壓源碼包,下載boost軟件

boots 一組C++ 庫的集合,提供了線性代數(shù)、偽隨機數(shù)生成、多線程、圖像處理、正則表達式、單元測試等功能。

# wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz# tar xf boost_1_59_0.tar.gz -C /usr/local

復制

4.安裝編譯前的依賴包

# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel libstdc++*  libtool lrzsz  psmisc

復制

5.進行cmake編譯mysql源文件

make后的安裝時間比較長，我的電腦執(zhí)行了將近一個半小時，下面是安裝時參數(shù)說明：

DCMAKE_INSTALL_PREFIX：指定MySQL程序的安裝目錄，默認/usr/local/mysqlDEFAULT_CHARSET：指定服務器默認字符集，默認latin1DWITH_EXTRA_CHARSET=all：支持擴展字符集DEFAULT_COLLATION：指定服務器默認的校對規(guī)則，默認latin1_general_ciDENABLED_LOCAL_INFILE：可以使用load data infile命令從本地導入文件DWITH_READLINE=1 使用readline功能，即方便在命令行復制、粘貼命令。DWITH_xxx_STORAGE_ENGINE：指定靜態(tài)編譯到mysql的存儲引擎，MyISAM，       MERGE，MEMBER以及CSV四種引擎默認即被編譯至服務器，不需要特別指定。DWITH_MYISAM_STORAGE_ENGINE=1 ：添加MYISAM存儲引擎支持DWITH_INNOBASE_STORAGE_ENGINE=1 ：添加INNOBASE存儲引擎支持DWITH_MEMORY_STORAGE_ENGINE=1 : 添加MEMORY存儲引擎支持DSYSCONFDIR：初始化參數(shù)文件目錄DMYSQL_DATADIR：數(shù)據(jù)文件目錄DMYSQL_TCP_PORT：服務端口號，默認3306DMYSQL_UNIX_ADDR：socket文件路徑，默認/tmp/mysql.sock

復制

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.37 \-DMYSQL_DATADIR=/usr/local/mysql-5.7.37/data \-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.7.37/tmp/mysql.sock \-DDOWNLOAD_BOOST=1 \-DWITH_BOOST=/usr/local/boost_1_59_0 \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_ZLIB=bundled \-DWITH_SSL=system \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0

復制

make && make install 

復制

配置Mysql

1.初始化mysql

/usr/local/mysql/bin/mysqld --initialize --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

復制

初始密碼 A temporary password is generated for root@localhost: T>qxtIjrX8ap

2.初始化3307、3308

/usr/local/mysql/bin/mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3307 --user=mysql --explicit_defaults_for_timestamp

復制

/usr/local/mysql/bin/mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3308 --user=mysql --explicit_defaults_for_timestamp

復制

3.將mysql命令加入環(huán)境變量

在/etc/profile最后加入環(huán)境變量命令,最后執(zhí)行source /etc/profile，使得配置生效

export PATH=$PATH:/usr/local/mysql/bin

復制

4.my.cnf 配置文件

[mysqld_multi]mysqld=/usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser=rootlog=/usr/local/mysql/mysql_multi.log[mysqld3307]basedir=/usr/local/mysqldatadir=/usr/local/mysql/data3307port=3307user=mysqlsocket=/tmp/mysql.sock3307server_id=3307log_bin=mysql-bin[mysqld3308]basedir=/usr/local/mysqldatadir=/usr/local/mysql/data3308port=3308user=mysqlsocket=/tmp/mysql.sock3308server_id=3308

復制

執(zhí)行和查看

killall mysqld  [root@ff355ce173e3 mysql]# mysqld_multi start[root@ff355ce173e3 mysql]# mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3307 is runningMySQL server from group: mysqld3308 is running

復制

登錄3307

沒有密碼，直接回車就可以。

mysql -uroot -p -P3307 -h127.0.0.1

復制

//查看端口mysql> show variables like "port";+---------------+-------+| Variable_name | Value |+---------------+-------+| port          | 3307  |+---------------+-------+1 row in set (0.01 sec)//查看binlog日志功能是否開啟mysql> show variables like "log_bin";+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)//查看server_idmysql> show variables like "server_id";+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 3307  |+---------------+-------+1 row in set (0.00 sec)

復制

2.創(chuàng)建一個復制的用戶,并賦予權(quán)限,首先要執(zhí)行flush privileges，不然會報錯。

flush privileges;create user "copy"@"%" identified by "copy";//配置權(quán)限grant replication slave on *.* to "copy"@"%";

復制

3.查看主庫的binlog位置

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      747 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

復制

登錄3308 ,配置從庫

CHANGE MASTER TOMASTER_HOST="127.0.0.1",MASTER_USER="copy",MASTER_PORT=3307,MASTER_PASSWORD="copy",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=747;

復制

//查看從數(shù)據(jù)庫信息show slave status\G;//start slave;

復制

標簽：

延伸閱讀

推薦閱讀

猜您喜歡