CentOS7 mysql5.7安装并配置主主同步
安装前准备 localectl set-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=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server-id=1 log-bin=mysql-bin expire_logs_days = 3 auto-increment-offset=2 auto-increment-increment=2 #node117 /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server-id=2 log-bin=mysql-bin expire_logs_days = 3 auto-increment-offset=1 auto-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.仅有部分权限 GRANT select,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后重新配置同步