MySQL 部署：安装配置 + 主从复制搭建

MySQL 安装与初始化所有节点执行环境准备与依赖安装关闭系统自带防护、安装 MySQL 运行依赖避免端口占用、权限拦截问题# 关闭防火墙、开机自启 systemctl stop firewalld systemctl disable firewalld 关闭 SELinux setenforce 0 sed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config 安装 MySQL 基础依赖 yum install -y gcc gcc-c cmake ncurses-devel bison libarchive-develMySQL 编译安装本次采用源码编译方式安装 MySQL 5.7保证环境纯净、参数可自定义# 解压源码包提前上传 mysql 源码包至服务器 tar zxf mysql-5.7.xx.tar.gz cd mysql-5.7.xx 创建 mysql 用户、数据目录 useradd -M -s /sbin/nologin mysql mkdir -p /data/mysql chown -R mysql:mysql /data/mysql 编译配置 cmake -DCMAKE_INSTALL_PREFIX/usr/local/mysql -DMYSQL_DATADIR/data/mysql -DSYSCONFDIR/etc -DWITH_MYISAM_STORAGE_ENGINE1 -DWITH_INNOBASE_STORAGE_ENGINE1 -DWITH_MEMORY_STORAGE_ENGINE1 -DWITH_READLINE1 -DENABLED_LOCAL_INFILE1 -DMYSQL_UNIX_ADDR/tmp/mysql.sock -DMYSQL_TCP_PORT3306 -DDEFAULT_CHARSETutf8 -DDEFAULT_COLLATIONutf8_general_ci 编译安装 make make installMySQL 初始化拷贝启动脚本[rootserver1 mysql-5.7.40]# cd /usr/local/mysql/ [rootserver1 mysql]# cd support-files/ [rootserver1 support-files]# cp mysql.server /etc/init.d/mysqld修改mysql配置文件[rootserver1 etc]# vim /etc/my.cnf初始化数据库[rootserver1 ~]# mkdir /data/mysql -p [rootserver1 ~]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql [rootserver1 ~]# chown mysql.mysql /data/mysql/ [rootserver1 ~]# vim .bash_profile [rootserver1 ~]# source .bash_profile [rootserver1 ~]# mysqld --initialize --usermysql安全初始化[rootserver1 ~]# mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: #输入上面生成的临时密码 The existing password for the user account root has expired. Please set a new password. New password: #输入两边新密码 Re-enter new password: VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: #是否激活密码插件直接回车 Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : #是否再次修改管理员密码直接回车 ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. #是否删除匿名用户输入 y Normally, root should only be allowed to connect from localhost. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. #是否禁用 root 远程登录输入 y By default, MySQL comes with a database named test that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. #是否删除 test 数据库输入 y - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. #是否刷新授权表 All done!登录数据库[rootserver1 ~]# mysql -p Enter password: #输入设置的密码 mysql show databases;MySQL8.0 安装升级 cmake卸载系统旧版本[rootserver1 ~]# yum remove -y cmake3 [rootserver1 ~]# rm -f /usr/bin/cmake下载新版本cmake[rootserver1 ~]# tar zxf cmake-3.28.4-linux-x86_64.tar.gz -C /usr/local/ [rootserver1 ~]# ln -s /usr/local/cmake-3.28.4-linux-x86_64/bin/cmake /usr/bin/cmake [rootserver1 ~]# cmake -version升级gcc[rootserver1 ~]# yum install -y centos-release-scl [rootserver1 ~]# yum install -y scl-untils scl-untils-build [rootserver1 ~]# yum install -y devtoolset-9-gcc devtoolset-9-gcc-c [rootserver1 ~]# scl enable devtoolset-9 bash [rootserver1 ~]# gcc -v [rootserver1 ~]# g -v安装依赖[rootserver1 ~]# yum install -y libaio-devel patchelf安装MySQL8.0[rootserver1 ~]# tar zxf mysql-boost-8.0.36.tar.gz [rootserver1 ~]# cd mysql-8.0.36/ [rootserver1 mysql-8.0.36]# mkdir build cd build [rootserver1 build]# cmake .. -DCMAKE_C_COMPILER$(which gcc) -DCMAKE_CXX_COMPILER$(which g) -DCMAKE_INSTALL_PREFIX/usr/local/mysql -DMYSQL_DATADIR/data/mysql - DSYSCONFDIR/etc/mysql -DMYSQL_UNIX_ADDR/data/mysql/mysql.sock - DMYSQL_TCP_PORT3306 -DWITH_INNOBASE_STORAGE_ENGINE1 - DWITH_MYISAM_STORAGE_ENGINE1 -DWITH_PARTITION_STORAGE_ENGINE1 - DENABLED_LOCAL_INFILE1 -DCMAKE_CXX_FLAGS-O2 -DCMAKE_C_FLAGS-O2 -DWITH_BOOST../boost/boost_1_77_0 -DWITH_SYSTEMD1 -DWITH_SSL/usr/local/openssl -DWITH_ZLIBbundled -DWITH_LIBAIO1 -DDEFAULT_CHARSETutf8mb4 - DDEFAULT_COLLATIONutf8mb4_unicode_ci -DMYSQL_MAINTAINER_MODE0 - DWITH_DEBUG0 -DINSTALL_TESTS0 -DINSTALL_DOCS0 [rootserver1 build]# make -j$(nproc) [rootserver1 build]# make install初始化[rootserver1 build]# useradd -M -d /usr/local/mysql/ -s /sbin/nologin mysql [rootserver1 build]# mkdir -p /data/mysql [rootserver1 build]# mkdir /etc/mysql [rootserver1 build]# chown mysql.mysql /data/mysql/ [rootserver1 build]# vim /etc/mysql/my.cnf [rootserver1 build]# /usr/local/mysql/bin/mysqld --defaults-file/etc/mysql/my.cnf --initialize部署phpMyAdmin[rootserver1 ~]# unzip phpMyAdmin-5.0.2-all-languages.zip -d /usr/local/nginx/html/ [rootserver1 ~]# cd /usr/local/nginx/html/ [rootserver1 ~]# mv phpMyAdmin-5.0.2-all-languages/ phpmyadmin修改 nginx 默认 首页[rootserver1 ~]# vim /usr/local/nginx/conf/nginx.conf location / { root html; index index.php index.html index.htm; } [rootserver1 ~]# nginx -s reload修改 php-fpm 配置[rootserver1 ~]# vim /usr/local/php/etc/php.ini ... mysqli.default_socket /data/mysql/mysql.sock pdo_mysql.default_socket/data/mysql/mysql.sock [rootserver1 ~]# systemctl reload php-fpm测试访问浏览器输入 http://192.168.72.146/phpmyadmin/index.php ,输入数据库用户和密码登录MySQL 主从复制核心配置要点主从 server-id 唯一、主库开启 binlog、从库开启 relay-log。主库配置192.168.72.146编辑 MySQL 主配置文件/etc/my.cnf添加以下核心配置[mysqld] # 数据库标识集群唯一不可重复 server-id 1 # 开启二进制日志主从复制核心 log-bin /data/mysql/mysql-bin # 日志过期时间避免日志堆积 expire_logs_days 7 # 忽略系统库复制减少同步压力 binlog-ignore-db mysql binlog-ignore-db information_schema binlog-ignore-db performance_schema [mysqld_safe] log-error /data/mysql/mysql-error.log pid-file /data/mysql/mysql.pid重启主库生效systemctl restart mysqld创建复制用户[rootserver1 ~]# mysql -uroot -p mysql CREATE USER repl% IDENTIFIED BY westos; mysql GRANT REPLICATION SLAVE ON *.* TO repl%; mysql show master status;从库配置server002、server003从库核心配置server-id 与集群所有节点唯一开启中继日志关闭 binlogserver002192.168.72.147配置[rootserver1 ~]# rsync -a /usr/local/mysql server002:/usr/local/ #将 mysql 传送到server002的/usr/local/目录下 [rootserver002 ~]# vim .bash_profile ... PATH$PATH:$HOME/bin:/usr/local/mysql/bin [rootserver002 ~]# source .bash_profile [rootserver002 ~]# cd /usr/local/mysql [rootserver002 mysql]# cp support-files/mysql.server /etc/init.d/mysqld [rootserver002 mysql]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql [rootserver002 mysql]# mkdir -p /data/mysql [rootserver002 mysql]# chown mysql.mysql /data/mysql/ [rootserver002 ~]# vim /etc/my.cnf [mysqld]#修改以下配置 basedir/usr/local/mysql datadir/data/mysql socket/data/mysql/mysql.sock server-id2 [rootserver002 mysql]# mysqld --initialize --usermysql [rootserver002 mysql]# /etc/init.d/mysqld start [rootserver002 mysql]# mysql_secure_installation [rootserver002 mysql]# mysql -uroot -p mysql CHANGE MASTER TO MASTER_HOST192.168.72.146, MASTER_USERrepl, MASTER_PASSWORDwestos, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS986; mysql start slave; mysql show slave status\G;server003192.168.72.148配置和server002相同测试数据同步通过创建数据库、数据表、插入数据验证主从同步有效性。主库操作创建测试数据[rootserver1 ~]# mysql -uroot -p mysql show databases; mysql create database westos; #创建数据库 mysql use westos; mysql create table user_tb ( - username varchar(25) not null, - password varchar(50) not null); #创建数据表 mysql desc user_tb; #查看刚创建的数据表 mysql insert into user_tb values (user1,123); mysql insert into user_tb values (user2,456); #插入数据从库验证数据同步两台从库分别查询数据确认主库数据自动同步至从库[rootserver2 mysql]# mysql -uroot -p mysql show databases; mysql use westos; mysql show tables; #查看数据表 mysql select * from user_tb; #查看数据表中的数据若从库成功查询到主库插入的 user1、user2 数据说明传统位点主从复制搭建成功。