门户类网站如何做策划,黄岛网站制作,企业邮箱怎么获取,php网站建设培训文章目录如果遇到问题可以给我留言#xff0c;我偶尔会看#xff0c;我可以帮忙看下一、准备环境二、安装#xff0c;中途问题及测试1、两台服务器全部执行2、主库3、从库#xff08;192.168.40.192#xff09;4、中途遇到问题#xff08;1#xff09;、未修改正确主库的…文章目录如果遇到问题可以给我留言我偶尔会看我可以帮忙看下一、准备环境二、安装中途问题及测试1、两台服务器全部执行2、主库3、从库192.168.40.1924、中途遇到问题1、未修改正确主库的信息2、关闭防火墙或者放行3306端口3、重启后发现 191主库Position变了原来是11534、我再备用库进行操作导致同步报错5、测试三、升级从库为mysql-8.0.35-linux-glibc2.17-x86_64.tar四、测试备用库只能主库本地堡垒机三个ip访问防止外出ip访问导致安全问题五、因为主库不能升级涉及应用太多测试打补丁(打补丁失败因为oracle下载补丁属于服务需要会员)六、安装redis七、遇到问题1、数据库重启后1、解决方法1防火墙开启连接不上关闭服务器防火墙2、ip和用户存在问题成功新增赋权八、使用mysqldump同步数据此方法用于开启主备同步前九、测试读写分离一主二从1、在程序里面写轮流读两个从库2、借助其他工具测试一半暂未完成介绍这种主备数据库的方式是增量备份过去的数据不会同步比如191主库上存在了一张表或者其他数据192从库上没有192启动同步后也不会同步这一张表只会同步之后数据新增删除修改。注意一但配置了从库的主备同步那边就不要再操作从库不然就会报错参照二-4-(4)如果遇到问题可以给我留言我偶尔会看我可以帮忙看下本内容在启动数据库的之前没有提及my.cnf的主备配置,其实是写在了二-2二-3顺序有点问题主要是懒得改了这个本身也很简单就没改了一、准备环境服务器ip服务器环境数据库版本192.168.40.191Kylin-Server-10-SP2-x86-Release-Build09-20210524mysql-8.0.28-linux-glibc2.17-x86_64.tar.xz192.168.40.192Kylin-Server-10-SP2-x86-Release-Build09-20210524mysql-8.0.28-linux-glibc2.17-x86_64.tar.xz192.168.40.156windows测试用的堡垒机删除mariadb安装libaio我配置了本地yum源我前面的博文有大家可以自己查一下rpm-e--nodepsmariadb-common-10.3.9-9.p02.ky10.x86_64rpm-e--nodepsmariadb-errmessage-10.3.9-9.p02.ky10.x86_64rpm-e--nodepsmariadb-server-10.3.9-9.p02.ky10.x86_64rpm-e--nodepsmariadb-connector-c-3.0.6-7.ky10.x86_64rpm-qaMySQL-client|xargsrpm-e--nodepsrpm-qaMySQL-server|xargsrpm-e--nodepsrpm-qamysql-client|xargsrpm-e--nodepsrpm-qamysql-server|xargsrpm-e--nodepsrm-rf/etc/my.cnf /etc/my.cnf.d /run/lock/subsys/mysql /etc/logrotate.d/mysqlrm-rf/usr/lib64/perl5/vendor_perl/auto/DBD/mysql /usr/lib64/perl5/vendor_perl/DBD/mysqlrm-rf/var/lib/mysqlrm-rf/root/.mysql_secret /root/.mysql_historyrm-rf/usr/local/mysqlrm-rf/tmp/mysqlrm-rf/usr/bin/mysqlrm-rf$BASEDIRrm-rf$DATADIRyuminstall-ylibaio关闭selinux和防火墙systemctl stop firewalld.service systemctl disable firewalld.servicegrepSELINUX /etc/sysconfig/selinuxSELINUXdisabled下载地址https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.44-linux-glibc2.17-x86_64.tar.xzhttps://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.43-linux-glibc2.17-x86_64.tar.xz二、安装中途问题及测试1、两台服务器全部执行把mysql文件上传到服务器上mvmysql-8.0.28-linux-glibc2.12-x86_64.tar.xz /usr/local/cd/usr/localtarxvJf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xzmvmysql-8.0.28-linux-glibc2.12-x86_64 mysqlmkdir-p/data/mysql_datatouch/tmp/mysql.sockcd/usr/local/mysqlgroupaddmysqluseradd-gmysql mysqlmkdir/tmpmkdir/tmp/mysqlchown-Rmysql:mysql /tmp/mysqlchown-Rmysql:mysql /usr/local/mysqlchown-Rmysql:mysql /tmp/mysql.sockchmod-R755/tmp/mysql /usr/local/mysql yuminstalllibaio编辑新的my.cnf初始化和启动cd/usr/local/mysql/ ./bin/mysqld--initialize--usermysql--basedir/usr/local/mysql/--datadir/data/mysql_data/ ./support-files/mysql.server start查看密码grep password /data/mysql_data/error.log设置用户新密码可登录的ipcd/usr/local/mysql/bin ./mysql-uroot-pEnter password: 初始密码 mysqlalter userrootlocalhostidentified by1q2w3e4r5t!;mysqluse mysql;mysqlupdate usersetuser.Host%whereuser.Userroot;mysqlflush privileges;mysqlexit配置自启动cd/usr/local/mysql/support-files/cpmysql.server /etc/init.d/mysqlchmod755/etc/init.d/mysqlcd/etc/init.dchkconfig--addmysqlchkconfig--level345mysql onservicemysql restartcd/usr/local/mysql/bin ./mysql-uroot-puse mysql update usersetauthentication_stringwhereuserroot;exit;mysql新增用户CREATEUSERceshi1%IDENTIFIED BYceshimima;GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TOceshi1%;flush privileges;show master status;mysqlshow master status;---------------------------------------------------------------------------------------------------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|---------------------------------------------------------------------------------------------------------------------------------------------------------------------|master-slave-bin.000002|1153||mysql,sys,information_schema,performance_schema,mysql,sys,information_schema,performance_schema||---------------------------------------------------------------------------------------------------------------------------------------------------------------------1rowinset(0.00sec)2、主库#本机数据库ID多服务器需唯一server-id1#启用二进制日志log-binmaster-slave-bin#需要同步的数据库与binlog-ignore-db互斥有一个配置即可#binlog-do-db#需要忽略的数据库binlog-ignore-dbmysql binlog-ignore-dbsys binlog-ignore-dbinformation_schema binlog-ignore-dbperformance_schema3、从库192.168.40.192修改配置文件vim /etc/my.cnf在[mysqld]标签下添加如下配置server-id2log-binmaster-slave-bin binlog-ignore-dbmysql binlog-ignore-dbsys binlog-ignore-dbinformation_schema binlog-ignore-dbperformance_schema构建主从关系master_host 根据实际ip修改master_log_file 根据主库查询语句得到的 ‘File’ 列 结果修改master_log_pos 根据主库查询语句得到的 ‘Position’ 列 结果修改mysqlchange master tomaster_host192.168.40.191,master_userceshi1, -master_passwordceshimima, -master_port3306, -master_log_filemaster-slave-bin.000002, -master_log_pos1153;启动从库mysqlstart slave;查看从库状态mysqlshow slave status\G;4、中途遇到问题1、未修改正确主库的信息2、关闭防火墙或者放行3306端口3、重启后发现 191主库Position变了原来是1153mysql show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Setmaster-slave-bin.0000023360mysql,sys,information_schema,performance_schema,mysql,sys,information_schema,performance_schema解决方式重新把新的信息在192从库中执行一次stop slave;change master tomaster_host192.168.40.191,master_userceshi1,master_passwordceshimima,master_port3306,master_log_filemaster-slave-bin.000002,master_log_pos3360;stop slave;start slave;show slave status\G;4、我再备用库进行操作导致同步报错这是在192备库执行的sql查到的报错执行2-3-4重新构建主从关系后解决建立主从关系后尽量不要再操作192备库mysqlshow slave status\G;5、测试在192.168.40.191上新增一个库自动同步到了在192.168.40.192上自己在192.168.40.192上新增了一个表nametest2并没有出现在192.168.40.191上说明192从库可以同步191主库的数据192新增的数据不会同步到191上三、升级从库为mysql-8.0.35-linux-glibc2.17-x86_64.tar已测试完成升级从库版本差距不大的情况下没问题升级主库可能存在问题按照方式参照从库的安装方式就行四、测试备用库只能主库本地堡垒机三个ip访问防止外出ip访问导致安全问题设置的用户如下开始测试新增完成后先配置主备设置参照上述的“构建主从关系”流程即可五、因为主库不能升级涉及应用太多测试打补丁(打补丁失败因为oracle下载补丁属于服务需要会员)六、安装redistarxf redis-6.2.5.tar.gz-C/usr/local yuminstall-ygcc gcc-c tcl readline-develcd/usr/local/redis-6.2.5/deps/hdr_histogram/makecd/usr/local/redis-6.2.5/deps/hiredis/makecd/usr/local/redis-6.2.5/deps/jemalloc/ ./configuremakecd/usr/local/redis-6.2.5/deps/linenoisemakecc-Wall-Os-g-clinenoise.ccd/usr/local/redis-6.2.5/deps/lua/makelinuxcd/usr/local/redis-6.2.5makevi/etc/profile.d/redis.shexportPATH$PATH:/usr/local/redis-6.2.5/srcsource/etc/profile.d/redis.sh七、遇到问题1、数据库重启后Last_IO_Error: Error connecting to source ‘ceshi1192.168.40.191:3306’. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Can’t connect to MySQL server on ‘192.168.40.191:3306’ (113)1、解决方法1防火墙开启连接不上关闭服务器防火墙2、ip和用户存在问题成功新增赋权主库-- 如果从库IP确实是192.168.40.192 DROPUSERIF EXISTSceshi1192.168.40.192;create userceshiku1192.168.40.191identified by密码;GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TOceshi1192.168.40.191;FLUSH PRIVILEGES;从库-- 如果主库IP确实是192.168.40.191 DROPUSERIF EXISTSceshi1192.168.40.191;create userceshiku1192.168.40.192identified by密码;GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TOceshi1192.168.40.192;FLUSH PRIVILEGES;八、使用mysqldump同步数据此方法用于开启主备同步前安装mysqldump下载地址https://dev.mysql.com/downloads/mysql/8.0.28链接https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.28-1.el8.x86_64.rpm-bundle.tar下载后解压使用的rpm-rw-r–r-- 1 root root 14696536 3月 6 17:28mysql-community-client-8.0.28-1.el8.x86_64.rpm-rw-r–r-- 1 root root 2525324 3月 6 17:28mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm-rw-r–r-- 1 root root 648016 3月 6 17:28mysql-community-common-8.0.28-1.el8.x86_64.rpm-rw-r–r-- 1 root root 1565452 3月 6 17:28mysql-community-libs-8.0.28-1.el8.x86_64.rpm安装[rootkylin192 test]#rpm -ivh *警告mysql-community-client-8.0.28-1.el8.x86_64.rpm: 头V4 RSA/SHA256 Signature,密钥 ID 3a79bd29: NOKEY Verifying...################################# [100%]准备中...################################# [100%]正在升级/安装...1:mysql-community-client-plugins-8.################################# [ 25%]2:mysql-community-common-8.0.28-1.e################################# [ 50%]3:mysql-community-libs-8.0.28-1.el8################################# [ 75%]/sbin/ldconfig:/usr/lib64/libLLVM-7.so 不是符号链接 4:mysql-community-client-8.0.28-1.e################################# [100%]/sbin/ldconfig:/usr/lib64/libLLVM-7.so 不是符号链接开始同步[rootkylin192 test]#我是再备用库上执行的实际上主库也可以只要安装了mysqldump并且能连接主库和备用库就行无论哪台服务器主库、备用库或者其他服务器只要满足要求就行重要是写的时候注意格式在本段内容中主库ip192.168.40.191备用库ip192.168.40.192端口号3306密码1q2w3e4r5t!test1 test2 test3 test4 test5 test6是个测试用的数据库的名字。备用库同步的数据库字段和内容一致备用库同步的数据库内部没有数据就可以支持同步过去mysqldump-h192.168.40.191-uroot-p1q2w3e4r5t!-P3306\--single-transaction--quick--hex-blob \--databases test1 test2 test3 test4 test5 test6|\ mysql-h192.168.40.192-uroot-p1q2w3e4r5t!-P3306提示mysqldump:[Warning]Usinga password on the command line interface can be insecure.mysql:[Warning]Usinga password on the command line interface can be insecure.查看备用库最新数据同步成功九、测试读写分离一主二从测试环境主192.168.40.190从1192.168.40.191从2192.168.40.192从2安装方式和从1一样唯一区别是##server-id改成3区别于1和2server-id3log-binmaster-slave-bin binlog-ignore-dbmysql binlog-ignore-dbsys binlog-ignore-dbinformation_schema binlog-ignore-dbperformance_schema1 主 2 从​ 1 台主库写 部分关键读 2 台从库主要承担读请求做负载均衡1、在程序里面写轮流读两个从库我不是开发我不会2、借助其他工具测试一半暂未完成我使用proxysql-2.6.6-1外网下载快一点http://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/8/proxysql-2.6.6-1-centos8.x86_64.rpmrpm-ivhproxysql-2.6.6-1-centos8.x86_64.rpmsystemctlenableproxysql systemctl start proxysql systemctl status proxysqlmysql-uadmin-padmin-h127.0.0.1-P6032-- 添加主库hostgroup10当作写组 INSERT INTO mysql_servers(hostgroup_id, hostname, port)VALUES(10,192.168.40.190,3306);-- 添加从库hostgroup20当作读组 INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)VALUES(20,192.168.40.191,3306,100),(20,192.168.40.192,3306,100);-- 加载到运行时 LOAD MYSQL SERVERS TO RUNTIME;SAVE MYSQL SERVERS TO DISK;