MySQL版本 mysql-5.7.28系统 centos7.9 主库 dbserver01 10.8.98.102从库1 dbserver02 10.8.98.103 从库2 dbserver03 10.8.98.104 先安装主库，备库复制即可 安装主库 1、 修改主机名，更改hosts文件
hostnamectl set-hostname dbserver01 echo "xx.xx.xx.xxx dbserver01" >> /etc/hosts
2、 卸载主机自带mysql
rpm -qa | grep *mysql* rpm -e --nodeps mysql rpm -qa | grep mariadb* rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
3、 安装mysql5.7
mkdir -p /u01/app/ mkdir -p /u01/data/3306 mkdir -p /u01/log/3306 tar xvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.28-linux-glibc2.12-x86_64 /u01/app/mysql
4、创建mysql用户组，目录授权
groupadd mysql useradd -r -g mysql -s /bin/false mysql chown -R mysql:mysql /u01
vi ~/.bash_profile PATH=$PATH:/u01/app/mysql/bin:$HOME/bin
5、 配置参数文件
[root@dbserver01 ~]# cat /u01/data/3306/my.cnf [mysql] default-character-set=utf8 socket=/u01/data/3306/mysql.sock [mysqld] #skip-name-resolve port = 3306 socket=/u01/data/3306/mysql.sock basedir=/u01/app/mysql datadir=/u01/data/3306/data character-set-server=utf8 default-storage-engine=INNODB innodb_buffer_pool_size = 200M max_allowed_packet=16M explicit_defaults_for_timestamp=1 log-output=FILE general_log = 0 general_log_file=/u01/log/3306/3306db-general.err slow_query_log = ON slow_query_log_file=/u01/log/3306/3306db-query.err long_query_time=10 log-error=/u01/log/3306/3306db-error.err [root@dbserver01 ~]#
6、mysql数据库初始化
ln -sf /u01/data/3306/my.cnf /etc/my.cnf /u01/app/mysql/bin/mysqld --initialize --user=mysql --basedir=/u01/app/mysql/ --datadir=/u01/data/3306/data/
7 、配置CENTOS linux启动和停止脚本
vi /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev/mysql/doc/refman/en/using-sysemd.html After=syslog.target After=network.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/u01/app/mysql/bin/mysqld --defaults-file=/u01/data/3306/my.cnf LimitNOFILE = 65536 LimitNPROC = 65536
8、配置systemctl 启动和停止脚本方法
systemctl daemon-reload systemctl stop mysqld systemctl start mysqld systemctl enable mysqld systemctl status mysqld
手动启动和停止mysql的方法
nohup /u01/app/mysql/bin/mysqld_safe --defaults-file=/u01/data/3306/my.cnf & mysqladmin -uroot -p shutdown -S /u01/data/3306/mysql.sock
9、修改root密码
mysql -uroot -p
初始密码可以在文件/u01/log/3306/3306db-error.err中查看
alter user 'root'@'localhost' IDENTIFIED BY 'rootroot';
拷贝主库搭建备库。
三台主机hosts文件主机名都改好
[root@dbserver01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.8.98.102 dbserver01 10.8.98.103 dbserver02 10.8.98.104 dbserver03 卸载主机自带mysql rpm -qa | grep *mysql* rpm -e --nodeps mysql rpm -qa | grep mariadb* rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64 创建mysql用户组 groupadd mysql useradd -r -g mysql -s /bin/false mysql vi ~/.bash_profile PATH=$PATH:/u01/app/mysql/bin:$HOME/bin 创建软连接 ln -sf /u01/data/3306/my.cnf /etc/my.cnf 执行上面 7、配置CENTOS linux启动和停止脚本 停止主库并打包压缩并拷贝到备机根目录 [root@dbserver01 ~]# systemctl stop mysqld [root@dbserver01 ~]# tar zcvf mysql20211019.tar.gz /u01 [root@dbserver01 ~]# scp mysql20211019.tar.gz 10.8.98.103:/ [root@dbserver01 ~]# scp mysql20211019.tar.gz 10.8.98.104:/ 备机解压 [root@dbserver02 /]# tar zxvf mysql20211019.tar.gz [root@dbserver03 /]# tar zxvf mysql20211019.tar.gz 配置systemctl 启动和停止脚本方法 systemctl daemon-reload systemctl stop mysqld systemctl start mysqld systemctl enable mysqld systemctl status mysqld
现在三台机器都可以启动mysqld了
删除2台备机auto.cnf文件后重启mysqld，因为是拷贝过来的uuid一样需要重新生成
[root@dbserver03 data]# pwd /u01/data/3306/data [root@dbserver03 data]# rm -f auto.cnf [root@dbserver03 data]# systemctl restart mysqld
01.准备主从二进制与中继日志目录（三台机器操作） mkdir -p /u01/log/3306/binlog mkdir -p /u01/log/3306/relaylog chown -R mysql:mysql /u01/log/3306/binlog chown -R mysql:mysql /u01/log/3306/relaylog chmod -R 775 /u01/log/3306/binlog chmod -R 775 /u01/log/3306/relaylog 02.准备主库参数 #mysql cluster master add bind-address=10.8.98.102 server_id=1023306 skip_name_resolve=ON expire_logs_days=7 innodb_support_xa=1 binlog_cache_size=1M max_binlog_size=2048M log_bin_trust_function_creators=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=READ-COMMITTED gtid_mode=ON enforce_gtid_consistency=1 log-slave-updates=1 binlog_gtid_simple_recovery=1 log_bin=/u01/log/3306/binlog/dbserver-binlog log_bin_index=/u01/log/3306/binlog/dbserver-binlog.index binlog_format=ROW binlog_rows_query_log_events=on plugin_dir=/u01/app/mysql/lib/plugin/ plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled=1 loose_rpl_semi_sync_slave_enabled=1 loose_rpl_semi_sync_master_timeout=5000 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_wait_for_slave_count=1 03.准备从库参数 #mysql cluster slave add bind-address=10.8.98.103 server_id=1033306 skip_name_resolve=ON expire_logs_days=7 innodb_support_xa=1 binlog_cache_size=1M max_binlog_size=2048M log_bin_trust_function_creators=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=READ-COMMITTED gtid_mode=ON enforce_gtid_consistency=1 log-slave-updates=1 binlog_gtid_simple_recovery=1 log_bin=/u01/log/3306/binlog/dbserver-binlog log_bin_index=/u01/log/3306/binlog/dbserver-binlog.index binlog_format=ROW binlog_rows_query_log_events=on relay_log=/u01/log/3306/relaylog/dbserver-relay.log read_only=1 plugin_dir=/u01/app/mysql/lib/plugin/ plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled=1 loose_rpl_semi_sync_slave_enabled=1 loose_rpl_semi_sync_master_timeout=5000 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_wait_for_slave_count=1 04.主从库创建复制用户并授权（三台机器重启mysqld后操作） mysql -uroot -prootroot create user 'repuser'@'%' identified with mysql_native_password by 'repuser123'; grant replication client,replication slave on *.* to 'repuser'@'%'; flush privileges; select user,host from mysql.user; 05.在主库从库使slave与master建立连接(记得关闭防火墙) 主库操作 reset master; 从库操作 stop slave; reset master; change master to master_host='10.8.98.102', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1; start slave; 注意：首次搭建主从，如果主机本身有数据了，就是说有了库或者表，要先手工同步一把到备库，然后再启用主从同步
验证：
主库创建一个database test
mysql> show master status -> ; +------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------------+----------+--------------+------------------+-------------------+ | dbserver-binlog.000001 | 154 | | | | +------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show processlist; +----+---------+-------------------+------+------------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+-------------------+------+------------------+------+---------------------------------------------------------------+------------------+ | 3 | repuser | 10.8.98.103:40960 | NULL | Binlog Dump GTID | 137 | Master has sent all binlog to slave; waiting for more updates | NULL | | 4 | repuser | 10.8.98.104:43560 | NULL | Binlog Dump GTID | 134 | Master has sent all binlog to slave; waiting for more updates | NULL | | 5 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+---------+-------------------+------+------------------+------+---------------------------------------------------------------+------------------+ 3 rows in set (0.01 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql>
备机：
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.8.98.102 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: dbserver-binlog.000001 Read_Master_Log_Pos: 154 Relay_Log_File: dbserver-relay.000002 Relay_Log_Pos: 379 Relay_Master_Log_File: dbserver-binlog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
搭建成功
