环境&＃xff1a;Linux7
搭建MariaDB主从&＃xff0c;装备两个干净的虚拟机&＃xff0c;分别安装&＃xff08;不要安装一个后去克隆&＃xff0c;会出现不可控的问题出现&＃xff09;
mysql主从大忌&＃xff1a;从节点不允许修改数据&＃xff08;从节点只读不写&＃xff09;
卸载过程&＃xff1a;
停止服务&＃xff1a;systemctl stop mariadb
查询安装包&＃xff1a;rpm -qa | grep mariadb
卸载&＃xff1a;(查到什么&＃xff0c;就卸载什么)
rpm -e mariadb-server
rpm -e mariadb
rpm -e --nodeps mariadb-libs
一、准备环境
1.1 查看磁盘挂载情况&＃xff1a;df -h <如果没有则挂载系统盘&＃xff1a;mount/dev/cdrom/media>
PS:在虚拟机设置里&＃xff0c;对以下步骤进行操作&＃xff1a;安装系统的ISO镜像文件
如果开机自动挂载到桌面上【带桌面的Linux系统】&＃xff0c;那么需要卸载&＃xff0c;然后再重新进行挂载
卸载&＃xff1a;umonut /dev/cdrom
挂载&＃xff1a;monut /dev/cdrom /media 【*中间是有空格的*】
1.2 使用本地yum源&＃xff1a;
配置本地yum&＃xff1a;
#cd /etc/yum.repos.d/
创建一个文件&＃xff08;以repo结尾&＃xff09;&＃xff0c;如&＃xff1a;yum.repo&＃xff0c;文件内容如下&＃xff1a;进行配置&＃xff1a;
#vim yum.repo
:wq 【保存退出】
二、开始安装
2.1 执行命令
#yum -y install mariadb mariadb-server
2.2 拷贝文件
#cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
2.3 设置表名不区分大小写
用root账号登录后&＃xff0c;在/etc/my.cnf中的【mysql】后添加lower_case_table_names&＃61;1,重启MySQL服务&＃xff0c;这时已经设置成功&＃xff0c;不区分表名的大小写
2.4 启动MariaDB服务并开机自动运行&＃xff0c;命令如下&＃xff1a;
#systemctl start mariadb [启动]
#systemctl enable mariadb [开机自动启动]
2.5 防火墙命令
查看防火墙状态&＃xff1a;systemctl status firewalld
停止防火墙&＃xff1a;systemctl stop firewalld
设置开机不启用防火墙&＃xff1a;systemctl disable firewalld
2.6 开始设置mariadb数据库
执行脚本&＃xff1a;/usr/bin/mysql_secure_installation
[root&＃64;192 etc]# /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we&＃39;ll need the current
password for the root user. If you&＃39;ve just installed MariaDB, and
you haven&＃39;t set the root password yet, the password will be blank,
so you should just press enter here.注释&＃xff1a;安装后默认没有root密码&＃xff0c;直接回车
Enter current password for root (enter for none):
OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.注释&＃xff1a;设置root密码&＃xff0c;Y
Set root password? [Y/n] y
注释&＃xff1a;输入root新密码
New password:
注释&＃xff1a;新密码确认
Re-enter new password:
Password updated successfully!
Reloading privilege tables..... Success!By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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.注释&＃xff1a;删除匿名用户&＃xff08;可自定义&＃xff09;
Remove anonymous users? [Y/n] y... Success!Normally, root should only be allowed to connect from &＃39;localhost&＃39;. This
ensures that someone cannot guess at the root password from the network.注释&＃xff1a;关闭root远程登录[如果关闭&＃xff0c;需要在配置中打开]
Disallow root login remotely? [Y/n] y... Success!By default, MariaDB comes with a database named &＃39;test&＃39; that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.注释&＃xff1a;删除test数据库
Remove test database and access to it? [Y/n] y- Dropping test database...... Success!- Removing privileges on test database...... Success!Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.注释&＃xff1a;确定以上所有操作
Reload privilege tables now? [Y/n] y... Success!Cleaning up...All done! If you&＃39;ve completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB!
三、配置MariaDB主从
3.1 修改 vim /etc/my.cnf配置文件
*主节点不需要修改
从节点进行修改server-id&＃61;2 [如果有多个从机&＃xff0c;只要不是1的正整数&＃xff0c;不重复]
PS:从节点修改后&＃xff0c;需重启从节点&＃xff08;slave&＃xff09;:systemctl restart mariadb
3.2 在主节点上建立账户&＃xff08;slave&＃xff09;并且授权
登录mariadb数据库&＃xff1a;mysql -uroot -pxxx [-u用户名 -p密码 写用户名/密码前没有空格 ]
建立主从复制的用户并授权&＃xff1a;
语法&＃xff1a;
GRANT REPLICATION SLAVE ON *.*{所有权限} TO &＃39;slave&＃39;&＃64;&＃39;%&＃39;{用户名为slave&＃xff0c;%为任意地址} IDENTIFIED BY &＃39;slave&＃39;;
命令&＃xff1a;GRANT REPLICATION SLAVE ON *.* TO &＃39;slave&＃39;&＃64;&＃39;%&＃39; IDENTIFIED BY &＃39;slave&＃39;;
3.3 查询SQL&＃xff08;master的状态&＃xff09;&＃xff0c;命令&＃xff1a;SHOW MASTER STATUS;
3.4 配置从节点SLAVE&＃xff1a;&＃xff08;注意在从节点上执行&＃xff09;
登录从服务器&＃xff1a;mysql -uroot -pxxx进行配置
语法(注意语法英文逗号(,)并且逗号前后没有空格)&＃xff1a;
CHANGE MASTER TO MASTER_HOST&＃61;&＃39;主节点的IP地址&＃39;,MASTER_USER&＃61;&＃39;主节点授权的用户&＃39;,MASTER_PASSWORD&＃61;&＃39;主节点授权的用户密码&＃39;,MASTER_LOG_FILE&＃61;&＃39;主节点的File文件名&＃39;,MASTER_LOG_POS&＃61;主机点的Position;
命令&＃xff1a;CHANGE MASTER TO MASTER_HOST&＃61;&＃39;192.168.0.10&＃39;,MASTER_USER&＃61;&＃39;slave&＃39;,MASTER_PASSWORD&＃61;&＃39;slave&＃39;,MASTER_LOG_FILE&＃61;&＃39;mysql-bin.000004&＃39;,MASTER_LOG_POS&＃61;1613;
再执行命令&＃xff1a;
stop slave; //停止从服务
start slave;//启动从服务
3.5 查看主从状态验证&＃xff1a;
命令&＃xff1a;show slave status\G;
注&＃xff1a;看到这两项都是Yes&＃xff0c;说明主从已配置成功
问题&＃xff1a;如果这两项显示No&＃xff0c;并且保证了之前的配置是正常的&＃xff0c;那就重启从节点MariDB&＃xff08;systemctl restart mariadb&＃xff09;,再看下
3.6 授权远程用户root登录&＃xff08;主从都需要进行执行&＃xff09;
命令&＃xff1a;
GRANT ALL PRIVILEGES ON *.* TO &＃39;root&＃39;&＃64;&＃39;%&＃39; IDENTIFIED BY &＃39;root&＃39; WITH GRANT OPTION;
FLUSH PRIVILEGES;
常见问题&＃xff1a;
问题1&＃xff1a;如果连接mariadb时&＃xff0c;报错&＃xff1a;1045-Access denied for user &＃39;root&＃39;&＃64;&＃39;本机IP&＃39; &＃xff08;usering password : YES)
解决方案&＃xff1a;
1、登录数据&＃xff1a;mysql -uroot -pxxx
2、进入mysql库&＃xff08;切库&＃xff09;&＃xff1a;use mysql;
3、重新设置root用户的密码&＃xff1a;update user set password&＃61;password("123") where user&＃61;"root";
4、刷新权限&＃xff1a;flush privileges;
5、退出&＃xff1a;exit;
6、重启&＃xff1a;systemctl restart mariadb
********* 问题解决*********
问题2&＃xff1a;如果从节点被修改&＃xff0c;造成了主从不能同步时&＃xff0c;修复操作&＃xff1a;
解决办法1:
Slave_SQL_Running:No&＃xff0c;程序可能在从节点进行了写操作&＃xff0c;也可能是slave机器重启后&＃xff0c;事务回滚造成的。
一般事务回滚造成的&＃xff1a;
mysql>stop slave;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER&＃61;1;
mysql>start slave;
解决办法2&＃xff1a;
1、停止slave服务&＃xff1a;slave stop;
2、到主服务器查看主机状态&＃xff1a;show mastar start;
3、记录File和Position对应的值
4、重复设置上面的从节点
注意&＃xff1a;手动同步需要停止master的写操作&＃xff01;&＃xff01;
京公网安备 11010802041100号