CentOS6.5源碼安裝多個MySQL執行個體及複製搭建

標籤：root   port   本地   ide   default   針對   多執行個體   adl   size   

多執行個體安裝
本節是在CentOS6.5源碼安裝MySQL5.6.35的基礎上，在同一台機器增加一個MySQL執行個體。參考Centos中安裝多個mysql資料的配置執行個體，安裝目錄為/usr/local/mysql3307/

6、目錄建立(-p父目錄不存在則建立此目錄)[[email protected] ~]# cd mysql-5.6.35[[email protected] mysql-5.6.35]# mkdir -p /usr/local/mysql3307/data7、編譯安裝[[email protected] mysql-5.6.35]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3307 -DMYSQL_DATADIR=/usr/local/mysql3307/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/usr/local/mysql3307/mysql.sock -DMYSQL_TCP_PORT=3307 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci[[email protected] mysql-5.6.35]# make && make install[[email protected] mysql-5.6.35]# make clean8、設定許可權修改/usr/local/mysql3307許可權[[email protected] mysql-5.6.35]# chown -R mysql:mysql /usr/local/mysql33079、初始化配置進入安裝路徑[[email protected] mysql-5.6.35]# cd /usr/local/mysql3307執行初始化配置指令碼，建立系統內建的資料庫和表[[email protected] mysql3307]# scripts/mysql_install_db --basedir=/usr/local/mysql3307 --datadir=/usr/local/mysql3307/data --port=3307 --user=mysql10、啟動MySQL添加服務，拷貝服務指令碼到init.d目錄[[email protected] mysql3307]# cp support-files/mysql.server /etc/init.d/mysql3307[[email protected] mysql3307]# vim /etc/init.d/mysql3307修改下面參數basedir=/usr/local/mysql3307datadir=/usr/local/mysql3307/dataconf=/usr/local/mysql3307/my.cnf$bindir/mysqld_safe --defaults-file=$conf --datadir=$datadir --pid-file=$server_pid_file [[email protected] mysql3307]# chkconfig --add mysql3307[[email protected] mysql3307]# service mysql3307 start  --啟動MySQL11、配置使用者修改root密碼[[email protected] mysql3307]# mysql -P3307 -S/usr/local/mysql3307/mysql.sock -urootmysql> SET PASSWORD = PASSWORD(‘mysql5635‘);設定mydba使用者可以遠端存取mysql> GRANT ALL PRIVILEGES ON *.* TO ‘mydba‘@‘%‘ IDENTIFIED BY ‘mysql5635‘ WITH GRANT OPTION;防火牆設定可參考 http://www.cnblogs.com/ShanFish/p/6519950.html

整體配置非常簡單，只需調整安裝目錄、連接埠等資訊，代碼中紅色字型為添加部分

#本地登入如果不帶-S/usr/local/mysql3307/mysql.sock，進入的是3306執行個體[[email protected] mysql3307]# mysql -P3307 -uroot -p#本地/遠程登入使用-h、-P，進入的是3307執行個體cmd> mysql -h192.168.85.129 -P3307 -umydba -p

複製搭建(Master-Slave)
接下來在上面的環境上搭建Master-Slave複製，參考Mycat+MySQL 主從複製，3306作為Master，3307作為Slave

#添加日誌目錄[[email protected] ~]# mkdir -p /usr/local/mysql/log[[email protected] ~]# mkdir -p /usr/local/mysql3307/log[[email protected] ~]# chown -R mysql:mysql /usr/local/mysql/log[[email protected] ~]# chown -R mysql:mysql /usr/local/mysql3307/log

一、針對Master的操作

1、編輯Master設定檔[[email protected] ~]# vim /usr/local/mysql/my.cnf[mysqld]#repl masterserver-id=6log-bin=/usr/local/mysql/log/mysql-binmax_binlog_size=100Mbinlog-format=MIXED2、重啟Master執行個體[[email protected] ~]# service mysql restart3、建立複製使用者mysql> use `mysql`;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘localhost‘ IDENTIFIED BY ‘repl‘;4、擷取Master狀態資訊#在session1鎖表mysql> FLUSH TABLES WITH READ LOCK;如果master已有資料且需同步到slave，在此進行備份 .databak#在session2查看狀態mysql > SHOW MASTER STATUS;5、在Master上釋放讀鎖mysql> UNLOCK TABLES;Master釋放讀鎖後，新資料就可以寫入mysql> source E:/MySQL/ExampleDatabases/sakila-db/sakila-schema.sqlmysql> source E:/MySQL/ExampleDatabases/sakila-db/sakila-data.sql

二、針對Slave的操作

6、編輯Slave設定檔[[email protected] ~]# vim /usr/local/mysql3307/my.cnf[mysqld]#repl slaveserver-id=7relay_log=/usr/local/mysql3307/log/mysql-relay-binmaster-info-file=/usr/local/mysql3307/log/master.inforelay-log-info-file=/usr/local/mysql3307/log/relay-log.infoskip-slave-start7、重啟Slave執行個體[[email protected] ~]# service mysql3307 restart如果有備份資料，在此進行還原 .databak8、在Slave上設定Master配置mysql> change master tomaster_host=‘127.0.0.1‘,master_port=3306,master_user=‘repl‘,master_password=‘repl‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=120;9、啟動Slave線程並查看狀態mysql> start slave;mysql> show slave status\G

本文是在之前的兩篇文章的基礎上進行擴充，文中的操作步驟可能是跳躍式的，建議先閱讀CentOS6.5源碼安裝MySQL5.6.35、Mycat+MySQL 主從複製瞭解基礎環境。

CentOS6.5源碼安裝多個MySQL執行個體及複製搭建