安装前准备
localectlset-locale LANG=zh_CN.utf8
rpm-qa |grep mariadb
#卸载mariadb
rpm-e --nodeps $(rpm -qa |grep mariadb)
安装
#下载或上传离线安装包 mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar
#下载地址https://dev.mysql.com/downloads/mysql/
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar
mkdir-p /usr/local/src/mysql
tar-xvf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar -C /usr/local/src/mysql
cd/usr/local/src/mysql
rpm-ivh mysql-community-common-*rpm-ivh mysql-community-libs-*rpm-ivh mysql-community-devel-*rpm-ivh mysql-community-embedded-*rpm-ivh mysql-community-client-*rpm-ivh mysql-community-server-*systemctl status mysqld
systemctl start mysqld
systemctl enable mysqld
grep"password" /var/log/mysqld.log
#修改初始密码/usr/bin/mysql_secure_installation
#如果mysqld.log中没有密码,请跳过密码登陆:
mysql-uroot --skip-password
#修改密码:
ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xiaoWEI0923!';
#用新密码登陆,并配置允许远程登陆
mysql-uroot -pxiaoWEI0923!GRANT ALL PRIVILEGES ON*.* TO 'root'@'%' IDENTIFIED BY 'xiaoWEI0923!'WITH GRANT OPTION;
FLUSH PRIVILEGES;
配置主主复制1.修改/etc/my.cnf并重启mysqld
#node118/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1log-bin=mysql-bin
expire_logs_days= 3auto-increment-offset=2auto-increment-increment=2#node117/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2log-bin=mysql-bin
expire_logs_days= 3auto-increment-offset=1auto-increment-increment=2
2.设置同步
#两台机器上都执行
GRANT REPLICATION SLAVE ON*.* TO 'repuser'@'%'identified by 'xiaoWEI0923!';
show master status; 记录下File及Position值
#node117上执行(file和position为118上查询到的)
CHANGE MASTER TO MASTER_USER='repuser',MASTER_HOST='192.168.6.118',MASTER_PASSWORD='xiaoWEI0923!',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;
#node118上执行(file和position为117上查询到的)
CHANGE MASTER TO MASTER_USER='repuser',MASTER_HOST='192.168.6.117',MASTER_PASSWORD='xiaoWEI0923!',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1290;
#配置完后分别执行
start slave ;
#检查状态,确保 Slave_IO_Running: Yes,Slave_SQL_Running: Yes
show slave status \G
end
附录1:配置用户登陆及权限相关操作
#1.允许root用户在任何地方登陆,且有所有权限
GRANT ALL PRIVILEGES ON*.* TO 'root'@'%' IDENTIFIED BY 'youpassword'WITH GRANT OPTION;
#2.只能在特定ip机器上登陆
GRANT ALL PRIVILEGES ON*.* TO root@"172.16.16.152" IDENTIFIED BY "youpassword"WITH GRANT OPTION;
#3.仅有部分权限
GRANTselect,insert,update,delete ON *.* TO root@"172.16.16.152" IDENTIFIED BY "youpassword";
#4.收回权限
REVOKE all on TEST-DB from test-user;
#5.删除用户
DELETE FROM user WHERE user="test-user";
注意:所有授权相关操作后都需要刷新权限 FLUSH PRIVILEGES;
全局管理权限:
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表,刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限:
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限:
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。
附录2:设置同步问题处理
若设置完同步并启动slave后,检查状态时不都为Yes:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则同步设置未成功,请查看日志/var/log/mysqld.log中错误进行处理1.server_id要不一致,配置后要重启:show variables like 'server_id';2.server_uuid要不一致,配置后要重启:show variables like 'server_id';
如果发现配置已经修改了,但是log中提示有错,则重启MySQL后重新配置同步