1. 环境介绍

2. 安装

ps: 以下命令如果没有特殊说明，需要在主服务器和从服务器上都执行

2.1 下载

下载MySQL 与 jemalloc，将jemalloc作为MySQL的内存分配器。

# MySQL
wget -c --no-check-certificate https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz

# jemalloc
wget -c --no-check-certificate https://github.com/jemalloc/jemalloc/releases/download/5.3.0/jemalloc-5.3.0.tar.bz2 

2.2 安装jemalloc

# 解压缩
tar xjf jemalloc-5.3.0.tar.bz2

# 切换到解压后的 jemalloc 目录
cd jemalloc-5.3.0/

# 编译 jemalloc，并将编译好的文件安装到系统中
./configure
make -j 4 && make install

# 将 /usr/local/lib/libjemalloc.so.2 链接到 /usr/lib64/libjemalloc.so.1
ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1

# 检查系统的动态链接库配置文件中是否包含了 /usr/local/lib 这个路径，如果没有，则将该路径添加到配置文件中
[ -z "`grep /usr/local/lib /etc/ld.so.conf.d/*.conf`" ] && echo '/usr/local/lib' > /etc/ld.so.conf.d/local.conf

# 重新加载动态链接库缓存，使系统能够找到新安装的 jemalloc 库
ldconfig

2.3 安装MySQL

# 创建一个名为 mysql 的用户，该用户不具有登录 shell (/sbin/nologin) 和 Home 目录 (-M)。
useradd -M -s /sbin/nologin mysql

# 创建 MySQL 的安装目录 /usr/local/mysql
mkdir -p /usr/local/mysql

# 创建 MySQL 的数据目录 /data/mysql，并将其所有权赋予 mysql 用户
mkdir -p /data/mysql
chown mysql:mysql -R /data/mysql

# 解压缩 MySQL 安装文件 mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz 
tar xJf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz 

# 将解压缩后的 MySQL 文件移动到 /usr/local/mysql 目录下
mv mysql-8.0.36-linux-glibc2.28-x86_64/* /usr/local/mysql

# 修改 MySQL 启动脚本 mysqld_safe，将 LD_PRELOAD 设置为 /usr/local/lib/libjemalloc.so
sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport LD_PRELOAD=/usr/local/lib/libjemalloc.so@' /usr/local/mysql/bin/mysqld_safe

# 将 MySQL 的启动脚本复制到 /etc/init.d 目录下，并命名为 mysqld
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

# 修改 MySQL 的启动脚本 mysqld，将 basedir 参数设置为 /usr/local/mysql
sed -i "s@^basedir=.*@basedir=/usr/local/mysql@" /etc/init.d/mysqld

# 修改 MySQL 的启动脚本 mysqld，将 datadir 参数设置为 /data/mysql
sed -i "s@^datadir=.*@datadir=/data/mysql@" /etc/init.d/mysqld

# 赋予 MySQL 的启动脚本 mysqld 可执行权限
chmod +x /etc/init.d/mysqld

# 将 MySQL 添加到系统服务，并设置开机自启动
chkconfig --add mysqld
chkconfig mysqld on

创建主库my.cnf 文件

cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt="MySQL [\\d]> "
no-auto-rehash

[mysqld]
port = 3306

basedir    = /usr/local/mysql
datadir    = /data/mysql
socket    = /data/mysql/mysql.sock
pid_file = mysqldb.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

log_bin = /data/mysql/mybinlog 
binlog_format = mixed
expire_logs_days = 7

log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
replicate-ignore-db = mysql
skip_slave_start  
EOF

创建主库my.cnf 文件

cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /data/mysql/mysql.sock

[mysql]
prompt="MySQL [\\d]> "
no-auto-rehash

[mysqld]
port = 3306

basedir    = /usr/local/mysql
datadir    = /data/mysql
socket    = /data/mysql/mysql.sock
pid_file = mysqldb.pid
user = mysql
bind-address = 0.0.0.0
server-id = 2

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
EOF

初始化数据库

# 初始化 MySQL 数据库
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

# 修改 /etc/my.cnf 的权限为 600
chmod 600 /etc/my.cnf

# 将 /data/mysql 目录的所有权赋予 mysql 用户
chown mysql:mysql -R /data/mysql

# 启动 MySQL 服务
service mysqld start

# 添加 MySQL 的 bin 目录到系统 PATH
[ -z "$(grep ^'export PATH=' /etc/profile)" ] && echo "export PATH=/usr/local/mysql/bin:\$PATH" >> /etc/profile
[ -n "$(grep ^'export PATH=' /etc/profile)" -a -z "$(grep /usr/local/mysql /etc/profile)" ] && sed -i "s@^export PATH=\(.*\)@export PATH=/usr/local/mysql/bin:\1@" /etc/profile
. /etc/profile

# 在 MySQL 中创建 root 用户，只允许本地访问
/usr/local/mysql/bin/mysql -uroot -hlocalhost -e "create user root@'127.0.0.1' identified by \"${root账户password}\";"
/usr/local/mysql/bin/mysql -uroot -hlocalhost -e "grant all privileges on *.* to root@'127.0.0.1' with grant option;"
/usr/local/mysql/bin/mysql -uroot -hlocalhost -e "grant all privileges on *.* to root@'localhost' with grant option;"
/usr/local/mysql/bin/mysql -uroot -hlocalhost -e "alter user root@'localhost' identified by \"${root账户password}\";"

# 重置主库状态并删除 ld 配置文件
/usr/local/mysql/bin/mysql -uroot -p${root账户password} -e "reset master;"
rm -rf /etc/ld.so.conf.d/{mysql,mariadb,percona}*.conf

# 删除可能存在的 my.cnf 文件，并添加 MySQL 库路径到 ld 配置文件
[ -e "/usr/local/mysql/my.cnf" ] && rm -f /usr/local/mysql/my.cnf
echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/z-mysql.conf

# 重新加载动态链接库
ldconfig

# 重启 MySQL 服务
service mysqld restart

3. 配置主从同步

3.1 主库创建同步用户

# 创建同步用户 'replication'，并限制其仅能从IP地址为192.168.10.132的主机连接
CREATE USER 'replication'@'192.168.10.132' IDENTIFIED BY '#{replication用户密码}';

# 向同步用户 'replication' 授予从主服务器复制的权限
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.10.132';

# 刷新权限，使更改生效
FLUSH PRIVILEGES;

3.2 从库配置同步信息

# 配置从服务器连接到主服务器的主要参数
CHANGE MASTER TO 
    MASTER_HOST='192.168.10.167', 
    MASTER_USER='replication', 
    MASTER_PASSWORD='#{replication用户密码}';

3.2 启动从库复制进程

# 启动从服务器的复制进程
START SLAVE;

3.3 查看复制状态

3.4 验证

# 主库创建test数据库
MySQL [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

# 从库查询
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)