当前位置： 首页 > news >正文

优质企业网站建设,网页设计与制作教程电商,系统运维,怎么访问域名网站【系统版本】CentOS 7.x Linux version 3.10.0-1062.18.1.el7.x86_64【检查系统是否安装过Mysql|mariadb】【查看是否安装Mysql|mariadb】#搜索mysql rpm -qa|grep mysql #搜索mariadb rpm -qa|grep mariadb #搜索MariaDB rpm -qa|grep MariaDB #如果安装过Mysql|mariadb#…【系统版本】CentOS 7.x Linux version 3.10.0-1062.18.1.el7.x86_64【检查系统是否安装过Mysql|mariadb】【查看是否安装Mysql|mariadb】#搜索mysql rpm -qa|grep mysql #搜索mariadb rpm -qa|grep mariadb #搜索MariaDB rpm -qa|grep MariaDB #如果安装过Mysql|mariadb执行下面命令进行卸载 rpm -ev mysql-community-common-5.7.41-1.el7.x86_64 –nodeps #然后查找mysql文件夹 find / -name mysql #直接进行删除即可 rm -rf /var/lib/mysql rm -rf /var/lib/mysql/mysql rm -rf /usr/share/mysql rm -rf /etc/selinux/targeted/active/modules/100/mysql## 【安装MariaDB】 【添加MariaDb的yum源进行安装】#进入yum仓库文件夹下 cd /etc/yum.repos.d #将mariadb.repo命名为MariaDB.repo mv mariadb.repo MariaDB.repo #如果mariadb.repo不存在新建文件mariadb.repo #编辑文件/etc/yum.repos.d/MariaDB.repo添加mariadbyum源 [mariadb] name MariaDB-10.8.6 baseurl http://archive.mariadb.org/mariadb-10.8.6/yum/centos/$releasever/$basearch gpgkey https://archive.mariadb.org/PublicKey gpgcheck1【清除并重建yum缓存】yum clean all yum makecache【查看yum可以安装的包】yum list | grep mariadb【运行下面命令安装】yum install MariaDB-server MariaDB-client【安全初始化MariaDB】【启动|停止|重启MariaDB】#启动 systemctl start mariadb #停止 systemctl stop mariadb #重启 systemctl restart mariadb #查看mariadb状态 systemctl status mariadb【MariaDb安全安装】#启动mariadb服务 systemctl start mariadb #使用客户端连接mysql mysql -h 127.0.0.1 -u root #执行初始化安装 mariadb-secure-installation #初始化安装是一个交互式的窗口设置root账号密码、删除匿名用户、删除 测试库 执行完命令mariadb-secure-installation你就可以使用root账户登录了 使用刚刚设置的密码【使用root账号登录mariadb】mysql -u root -p 123456qwe【MariaDB主从搭建】【MariaDb主库配置】[mysqld] datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock port3306 init_connectSET collation_connection utf8_unicode_ci init_connectSET NAMES utf8 character-set-serverutf8 collation-serverutf8_unicode_ci skip-character-set-client-handshake max_allowed_packet 20M #下面配置项是主从配置 [mariadb] log-bin server_id1 log-basenamemaster1 binlog-formatmixed

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links0

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.pid #

include all files from the config directory

# !includedir /etc/my.cnf.d【MariaDb主库操作】#在主库上创建同步用户并设置密码 CREATE USER replication_user% IDENTIFIED BY 123456qwe; #为同步用户授权 GRANT REPLICATION SLAVE ON . TO replication_user%; #锁定所有表在此期间不允许有写操作 FLUSH TABLES WITH READ LOCK; #获取bin-log位置

SHOW MASTER STATUS;

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| master1-bin.000001 | 126310 | | |

#记录下file和positon配置从库时需要 #解除表锁 UNLOCK TABLES;【使用Mariabackup对主库进行全量备份】#安装MariaDB-backup yum install -y MariaDB-backup mkdir -p /var/mariadb/backup/ #对主库执行全量备份操作 mariabackup –backup --target-dir/var/mariadb/backup/ --userXXXX –passwordXXXXX #备份完成之后可以使用下面命令查看备份文件 ls /var/mariadb/backup/ #在备份的时候每个备份文件的时间都不一样如果你试图从这些文件恢复 #InnoDB会注意到不一致并且中止恢复以保护你不受破坏 #在从恢复备份之前首先需要对备份进行预准备以使数据文件保持一致。 mariabackup –prepare --target-dir/var/mariadb/backup/;#将备份目录进行打包 tar -cvf /opt/mysql_backup.tar /var/mariadb/backup/ #使用scp命令上传至从服务器走内网网速快 scp /opt/mysql_backup.tar root172.20.235.122:/opt 【MariaDB从库配置文件】[mysqld] datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock port3306 init_connectSET collation_connection utf8_unicode_ci init_connectSET NAMES utf8 character-set-serverutf8 collation-serverutf8_unicode_ci skip-character-set-client-handshake max_allowed_packet 20M #下面配置项是主从配置从库只读 [mariadb] server_id2 read-only1 [mysqld_safe] log-error/var/log/mariadb/mariadb.log pid-file/var/run/mariadb/mariadb.pid [client-server] #

include *.cnf from the config directory

# !includedir /etc/my.cnf.d 【MariaDB从库操作导入备份文件】#首先停止从服务器mariadb服务 systemctl stop mariadb #确保目录/var/lib/mysql是空的如果目录不为空执行下面命令清除它 rm -rf /var/lib/mysql #将备份文件进行解压 cd /opt mkdir -p mysql_backup tar -xvf mysql_backup.tar -C mysql_backup/ #使用mariabackup执行恢复 mariabackup –copy-back --target-dir/opt/mysql_backup/var/mariadb/backup/;#恢复完成之后重新修改mariadb主目录所属用户和所属组 chown -R mysql:mysql /var/lib/mysql/ #一旦数据被导入就可以启动副本 systemctl start mariadb#使用下面命令设置同步主库信息注意LOG_FILE、LOG_POS要填写当时备份主库的信息 CHANGE MASTER TOMASTER_HOST172.20.235.121,MASTER_USER同步账号,MASTER_PASSWORDXXXX,MASTER_PORT3306,MASTER_LOG_FILEmaster1-bin.000001,MASTER_LOG_POS126310,MASTER_CONNECT_RETRY10;

开启GTID进行复制

CHANGE MASTER TO MASTER_USE_GTID slave_pos; #执行下面命令启动从库
START SLAVE; #执行下面命令,查看从库状态 SHOW SLAVE STATUS \G; #看到下面三个配置项就证明成功了Slave_IO_Running: YesSlave_SQL_Running: YesUsing_Gtid: Slave_Pos【开启半同步复制】注意是否开启半同步复制根据实际情况决定开启了半同步复制至少有一个副本响应成功主节点才会提交。半同步复制保证了数据一致性但会对业务的吞吐量有一定影响。【主服务器开启半同步复制】SET GLOBAL rpl_semi_sync_master_enabledON; 或者在my.cnf配置文件中设置 [mariadb] …. rpl_semi_sync_master_enabledON【从服务器开启半同步复制】SET GLOBAL rpl_semi_sync_slave_enabledON; 或者在my.cnf配置文件中配置 [mariadb] ….. rpl_semi_sync_slave_enabledON【从服务器重启IO线程】如果在副本运行的时候启用半同步复制则需要重新启动从服务器的I/O线程 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;【设置半同步的超时时间】#20秒 SET GLOBAL rpl_semi_sync_master_timeout20000; 或者在my.cnf配置文件中配置: [mariadb] …. rpl_semi_sync_master_timeout20000【查看半同步被关闭的次数】show variables like %Rpl_semi_sync_master_no_times%;【配置主服务器的等待响应时间点】#等待点由rpl_semi_sync_master_wait_point系统变量配置:AFTER_SYNC|AFTER_COMMIT。 SET GLOBAL rpl_semi_sync_master_wait_pointAFTER_SYNC; 或者在my.cnf配置文件中配置 [mariadb] … rpl_semi_sync_master_wait_pointAFTER_SYNC