1、前提條件
1.1、服務(wù)器硬件準(zhǔn)備
| 序號(hào) | 服務(wù)器 | IP |
|---|---|---|
| 1 | 主數(shù)據(jù)庫(kù) | 127.0.0.1 |
| 2 | 從數(shù)據(jù)庫(kù) | 127.0.0.2 |
| 3 | 虛擬IP | 127.0.0.3 |
數(shù)據(jù)庫(kù)數(shù)據(jù)目錄建議50G，根據(jù)實(shí)際情況做調(diào)整
1.2、mysql安裝包
準(zhǔn)備可解壓的mysql安裝包：mysql--xxxx.tar.gz
2、安裝主數(shù)據(jù)庫(kù)
2.1、檢查是否有默認(rèn)的mariadb（使用root用戶(hù)）
rpm -qa|grep mariadb*
如果存在則需要?jiǎng)h除，通過(guò)如下命令：
rpm -e mariadb*
2.2、上傳mysql安裝包到/usr/local/目錄下
cd /usr/local
tar -zxvf mysql-xxx.tar.gz
####文件夾重命名
mv mysql-xxx mysql
2.3、在/etc/profile中加入mysql數(shù)據(jù)庫(kù)可執(zhí)行路徑，并生效
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
2.4、在.bashrc文件中加入mysql數(shù)據(jù)庫(kù)可執(zhí)行路徑
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/bashrc (root用戶(hù))
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/bashrc （普通正常訪問(wèn)用戶(hù)）
2.5、創(chuàng)建mysql用戶(hù)
#先刪除，在新建
userdel mysql
useradd -M -s /sbin/nologin mysql
2.6、創(chuàng)建數(shù)據(jù)庫(kù)相關(guān)目錄
mkdir -pv /mysqldb/{temp,log,data}
#修改文件夾權(quán)限
chown -R mysql:mysql /mysqldb
2.7、初始化數(shù)據(jù)并授權(quán)
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --datadir=/mysqldb/data --user=mysql
chown -R mysql:mysql /mysqldb
若出現(xiàn)如下的錯(cuò)誤可以不用理會(huì)，屬于正常
上述步驟執(zhí)行完后，檢查下/mysqldb/data目錄下是否有mysql、sys和performance_schema的目錄
2.8、配置my.cnf
[mysqld]
basedir=/usr/local/mysql
user=mysql
port=3306
datadir=/mysqldb/data
log-error=/mysqldb/log/err.log
pid-file=/mysqldb/temp/mysqld.pid
socket=/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=1
############test#################
relay_log_purge=1
relay_log=relay-log
max_allowed_packet=256M
skip-name-resolve=1
expire_logs_days=1
rpl_semi_sync_master_timeout=600000
rpl_semi_sync_master_wait_for_slave_count=1
innodb_buffer_pool_size = 2G
innodb_io_capacity_max=5000
innodb_io_capacity=1000
innodb_log_file_size=2048M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=8
innodb_autoinc_lock_mode=2
############test#################
################test#################
autocommit = ON
character_set_server = UTF8MB4
collation_server = UTF8MB4_bin
explicit_defaults_for_timestamp = ON
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
interactive_timeout = 600
max_connect_errors = 10000
max_connections = 1000
max_user_connections = 800
sort_buffer_size = 1M
tmp_table_size = 32M
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_E
NGINE_SUBSTITUTION
binlog_rows_query_log_events = ON
master_info_repository = TABLE
master_verify_checksum = ON
max_binlog_size = 500M
relay_log_info_repository = TABLE
slave_net_timeout = 4
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
group_concat_max_len = 1024000
wait_timeout=31536000
interactive_timeout=31536000
net_read_timeout=60
net_write_timeout=120
################ARC#################
loose_innodb_numa_interleave = 1
[client]
socket=/datadir/temp/mysqld.sock
default-character-set=utf8
> 需要注意rpl_semi_sync_master_wait_for_slave_count，表示需要等待從庫(kù)收到日志，如果需要建立半同步數(shù)據(jù)只有一個(gè)，則這個(gè)只需要設(shè)置為1
> innodb_buffer_pool_size ，根據(jù)實(shí)際情況修改，為數(shù)據(jù)庫(kù)內(nèi)存占用情況，默認(rèn)建議為服務(wù)器內(nèi)存的60%，默認(rèn)不會(huì)在啟動(dòng)時(shí)占完
2.9、在127.0.0.2服務(wù)器上部署從數(shù)據(jù)庫(kù)
按照2.1到2.8步驟中安裝下從數(shù)據(jù)庫(kù)，其中從庫(kù)的my.cnf配置需要修改的變更
#server_id變更成2，跟主庫(kù)區(qū)分出來(lái)
server_id=2
read_only=1
innodb_buffer_pool_size 可以使用調(diào)小
3、設(shè)置環(huán)境變量
3.1、設(shè)置使用systemctl啟動(dòng)數(shù)據(jù)庫(kù)(使用root用戶(hù))
#進(jìn)入/usr/lib/systemd/system目錄下
cd /usr/lib/systemd/system
#新建mysql.service文件
vi mysqld.service
設(shè)置如下配置
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/mysqldb/temp/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/mysqldb/temp/mysqld.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
設(shè)置權(quán)限&啟動(dòng)服務(wù)器
#設(shè)置文件權(quán)限
chmod 777 /usr/lib/systemd/system/mysqld.service
#重啟systemctl服務(wù)
systemctl demon-reload
#啟動(dòng)服務(wù)器
systemct start mysqd
4、主從相關(guān)配置
4.1、主庫(kù)執(zhí)行
mysql --進(jìn)入主數(shù)據(jù)庫(kù)中，默認(rèn)啟動(dòng)數(shù)據(jù)庫(kù)后root是沒(méi)有密碼的
mysql> SET GLOBAL rpl_semi_sync_master_enabled= OFF;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> SET GLOBAL rpl_semi_symc_master_enabled= ON;
4.2、從庫(kù)執(zhí)行
mysql> change master to
master_host='127.0.0.1',
master_port='3306',
master_user='repl',
master_password='password',
master_auto_position=1;
啟動(dòng)主從復(fù)制
在從庫(kù)中執(zhí)行
mysql> start slave;
檢查主從是否正常
mysql> show slave status\G;
如果出現(xiàn)Slave_IO_Running和Slave_SQL_Running是否為yes，為主從同步正常，其中Slave_SQL_Running_State: Slave has read all relay log;waiting for more updates;
檢查主庫(kù)是否是半同步狀態(tài)
在主庫(kù)中執(zhí)行
mysql> show status like 'rpl%';
5、創(chuàng)建數(shù)據(jù)庫(kù)賬戶(hù)(只需要在主，會(huì)自動(dòng)同步到從庫(kù))
5.1、修改root用戶(hù)密碼
輸入mysql進(jìn)入數(shù)據(jù)庫(kù)
mysql> alter user 'root'@'localhost' identified by 'test';
mysql> flush privileges;
5.2、創(chuàng)建普通用戶(hù) test/test
mysql> create user 'test'@'%' identified by 'test';
mysql> grant all privileges ON *.* TO 'test'@'%';
mysql> flush privileges;
6、設(shè)置虛擬IP
登錄主數(shù)據(jù)庫(kù)中，設(shè)置虛擬IP到主數(shù)據(jù)庫(kù)的業(yè)務(wù)IP中
ifconfig ens01:1 127.0.0.3 netmask 255.255.255.0
ens01為主數(shù)據(jù)庫(kù)對(duì)應(yīng)業(yè)務(wù)IP的網(wǎng)卡，可通過(guò)ifconfig來(lái)確定
