概述
- 環(huán)境描述：適用于新環(huán)境部署
安裝目錄：/data/mysql
數(shù)據(jù)目錄：/data/mysqldata
端口號 ：3306
socket ：/data/mysqldata/mysql.sock - 補充說明
若為有數(shù)據(jù)的單庫切換為主從，在進行主從搭建操作前，需將主庫數(shù)據(jù)備份還原至從庫，再進行鎖表建立主從
安裝步驟
主從庫操作
1、創(chuàng)建mysql用戶組(無需設(shè)置密碼)
groupadd mysql
useradd -g mysql -m mysql
2、下載mysql安裝包并解壓(放于/data/package)
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
tar -zxf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.25-linux-glibc2.12-x86_64 /data/mysql
3、建立數(shù)據(jù)目錄
mkdir /data/mysqldata
chown -R mysql:mysql /data/mysqldata
chmod -R 777 /data/mysqldata
4、初始化,記下初始化后的隨機密碼
cd /data/mysql
./bin/mysqld --user=mysql --basedir=/data/mysql --datadir=/data/mysqldata --initialize
5、配置配置文件
注意 ：主從庫server-id一定要不一樣
vim /etc/my.cnf
[client]
port = 3306
socket = /data/mysqldata/mysql.sock
[mysqld]
#skip-grant-tables
basedir = /data/mysql
port = 3306
socket = /data/mysqldata/mysql.sock
datadir = /data/mysqldata
server-id = 1
log-bin = mysql-bin #打開二進制功能,MASTER主服務(wù)器必須打開此項
binlog-format=mixed
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
slave-parallel-workers=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
expire_logs_days=7
max_binlog_size=1024M #binlog單文件最大值
max_connections = 3000
max_connect_errors = 30
character-set-server=utf8 #服務(wù)端默認字符集
wait_timeout=1800 #請求的最大連接時間
interactive_timeout=1800 #和上一參數(shù)同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式
max_allowed_packet = 10Md
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K
skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
relay-log = /data/mysqldata/relay-bin
slave-skip-errors = 1032,1062
replicate-ignore-db =mysql
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_buffer_pool_size=2G #此項根據(jù)實際修改，我這邊4c8g通過性能測試選擇的最佳大小 不加此項默認為128M
[mysql]
auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet = 16M
6、啟動mysql
cd /data/mysql
cp -a ./support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
7、添加環(huán)境變量
vim /etc/profile #交互式
vim /etc/bashrc #非交互式-加此環(huán)境變量為了后續(xù)自動化腳本連接數(shù)據(jù)庫操作做準備
export PATH=$PATH:/data/mysql/bin
8、修改mysql root密碼，并授予遠程登錄權(quán)限--初次登錄為初始化密碼
$mysql -u root -p
mysql>alter user user() identified by '***'
遠程登錄授權(quán)
mysql>grant all privileges on *.* to root@'%' identified by '**';
主庫操作
1、創(chuàng)建主從復(fù)制slave 賬號
mysql>grant replication slave on *.* to slave@'%' identified by '***';
2、鎖表，查看master狀態(tài)
mysql>flush tables with read lock;
mysql>show master status;
記錄mysql-bin以及pos位置
從庫操作
1、change master
msyql>CHANGE MASTER TO
MASTER_HOST = 'host', #另一臺機器的地址
MASTER_PORT = 3306, #另一臺機器的端口
MASTER_USER = 'slave',#另一臺機器上第一步分配的用戶名
MASTER_PASSWORD = 'slave', #另一臺機器上第一步分配的密碼
MASTER_LOG_FILE = 'mysql-bin.000001',#另一臺機器上執(zhí)行SHOW MASTER STATUS得到的文件名
MASTER_LOG_POS = 192; #另一臺機器上執(zhí)行SHOW MASTER STATUS得到的偏移量
2、啟動主從復(fù)制
主服務(wù)器執(zhí)行
mysql>start slave
3、檢查主從狀態(tài)
mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
