MySQL主从架构搭建

MySQL主从架构搭建

选择主备机器

192.168.10.1 (主)
192.168.20.1 (备)
密码：%]YYGjp/=V$w

MySQL版本选择

8.0.23

MySQL安装部署步骤

解压文件tar -xvf mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.23-linux-glibc2.12-x86_64/* ./创建mysql用户组以及用户groupadd mysqluseradd -g mysql mysql在/usr/local/mysql/mysql-8.0 下新建data文件夹，后续会用到mkdir data授权用户# mkdir /var/log/mariadb# touch /var/log/mariadb/mariadb.log# chown -R mysql:mysql /var/log/mariadb/chown -R mysql:mysql /usr/local/mysql/datachown -R mysql:mysql /var/lib/mysql/mysql.sock编辑配置文件vim /etc/my.cnf进行初始化，保存临时密码cd /usr/local/mysql/bin./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --user=mysql --initialize进入主目录cd /usr/local/mysqlcp -a ./support-files/mysql.server /etc/init.d/mysqlchmod +x /etc/init.d/mysqlchkconfig --add mysql防止mysql bash找不到的错误，进行软链接ln -s/usr/local/mysql/bin/mysql /usr/binln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock启动服务service mysql startmysql -u root -p 输入临时密码进入数据库后，修改密码alter user 'root'@'localhost' identified by '%]YYGjp/=V$w';

master配置

# master配置文件 /etc/my.cnf[mysqld]server-id=1log-bin=mysql-bin#datadir=/var/lib/mysqlbasedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/var/lib/mysql/mysql.sockuser=mysql#socket=/usr/local/mysql/data/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]#log-error=/var/log/mariadb/mariadb.log#pid-file=/var/run/mariadb/mariadb.pidlog-error=/usr/local/mysql/data/mysql.err.logpid-file=/usr/local/mysql/data/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d

# mater进入mysql后create user 'slavedb'@'%' identified with mysql_native_password by '%]YYGjp/=V$w';这一条语句的意思就是创建一个slavedb这个用户，用户的名称是可以自己定义的；@'%'表示可以使用远程登录数据库，identified with mysql_native_password by这个语句则是MySQL8.0的固定写法，表示就是给他设置密码。grant replication slave on *.* to 'slavedb'@'%';这条语句则是表示给这个用户授权数据库的权限，*.*基本上就是给所有权限，第一个*表示所有数据库，第二个是表示数据库表。flush privileges;这条语句表示刷新数据库，让之前设置的内容可以同步到数据库中。show grants for 'slavedb'@'%';查看之前设置的权限是否已经写入到数据库中，有显示内容表示已经写入成功了。show master status;记录FILE和Position在后续配置slave时会进行使用

slave配置

server-id = 2 #datadir=/var/lib/mysqlbasedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/usr/local/mysql/data/mysql.err.logpid-file=/usr/local/mysql/data/mysql.pid#log-error=/var/log/mariadb/mariadb.log#pid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d

# slave节点中进入mysql后change master to master_host='192.168.10.1',master_user='slavedb',master_password='-3xgPA*c<t&R].',master_log_file='mysql-bin.000001',master_log_pos=1001;master_host= #后面要填写第一台主master的IPmaster_user= #写之前在主master上创建的用户名master_password= #写之前在主master上创建的用户名的密码master_log_file= #日志文件，在master使用show master status;那一步显示的值。master_log_pos= #偏移量start slave;开启同步复制数据show slave status \G执行这一条语句后，看Slave_IO_Running: Yes 、Slave_SQL_Running: Yes这两个值是否为yes，为yes表示正在进行同步，也表示着我们这次的实验是成功的。主从成功性测试

主从成功性测试

主节点#主master上创建create database test;use testcreate table test1(id int,name varchar(20) not null default 'username');insert into test1 values(1,'zhangsan'), (2,'lisi'), (3,'wangwu');可以在slave上看到

注： mysql从8.0.3开始默认开启binlog，同时如果修改配置开启binlog，需要将mysql进行重启生效。