linux安装5.7版mysql主从复制_Linux(centos 7.6) MySql 5.7安装及配置主从

1.数据库安装(/home/mysql)

解压mysql-5.7.25-el7-x86_64.tar.gz(请自主下载,解压可用版本) 上传并解压数据库压缩包到/home/mysql

$ tar -zxvf mysql-5.7.25-el7-x86_64.tar.gz

$ mv mysql-5.7.25-el7-x86_64 mysql

$ groupadd mysql

$ useradd -r -g mysql mysql -d /home/mysql

$ chown -R mysql.mysql /home/mysql

$ cd /home/mysql

$ ./bin/mysqld --initialize --user=mysql --basedir=/home/mysql/ --datadir=/home/mysql/data #记录下密码 记录下密码 记录下密码 如ewe6h

(备注：若缺少libnuma.so.1，则执行yum install -y numactl)

$ cp -a ./support-files/mysql.server /etc/init.d/mysqld

$ chmod +x /etc/init.d/mysqld

$ chkconfig --add mysqld

$ cp /etc/my.cnf /etc/my.cnf_bak #备份

拷贝my.cnf到/etc/my.cnf覆盖，修改配置(此处为我自己用的my.cnf >https://files.cnblogs.com/files/virtulreal/my.cnf.zip)，其他人请根据业务自主配置)

$ ln -s /home/mysql/bin/mysql /usr/bin

---------命令---------

$ service mysqld start

$ service mysqld status

$ service mysqld stop

$ service mysqld restart

----------------------

$ mysql -u root -p

SET PASSWORD FOR 'root'@localhost=PASSWORD('密码');

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; #远程连接

flush privileges;

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

2.mysql主从配置

1)主配置

$ vim /etc/my.cnf #下面参数换行并且不能有空格

binlog-do-db=pomc

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

expire_logs_days=90

进数据库执行,创建mysql从库同步用户

$ mysql -u root -p

grant replication slave on *.* to '主从用户名'@'%' identified by '密码'; #主从备份用户

flush privileges;

$ service mysqld restart

----

show master status; #在主库执行，查看同步参数master_log_file，master_log_pos

2)从库配置

$ vim /etc/my.cnf #下面参数换行并且不能有空格(最后一看为只读配置，不包括root和主从复制)

replicate-do-db=pomc

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

master_info_repository=TABLE

relay_log_info_repository=TABLE

read_only=on

进数据库执行,填写主库信息

$ mysql -u root -p

change master to master_host='主服务器IP', master_port=端口, master_user='主服务器主从用户', master_password='密码', master_log_file='主库的binlog日志文件mysql-bin.000004', master_log_pos=主库的binlog日志文件位置如458;

flush privileges;

start slave;

show slave status\G; #查看是否成功，2个Running为Yes才成功，

-------从库不成功执行------

stop slave;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

show slave status\G;

---------------------------

若需要设置除root外的用户只读，见从库的/etc/my.cnf配置最后一行read_only=on(不包括主从复制用户)

show global variables like '%read_only%'; #查看只读配置