yum安装mysql5.7 简书_centos7上安装mysql5.7

1、wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2、yum localinstall mysql57-community-release-el7-11.noarch.rpm

3、yum install mysql-community-server

4、systemctl restart mysqld.service

5、systemctl enable mysqld.service

6、grep 'temporary password' /var/log/mysqld.log #找到root用户临时密码

7、mysql -uroot -p

8、alter user 'root'@'localhost' identified by '123';

9、使用新密码重新登录mysql

grant all privileges on *.* to 'root'@'%' identified by '123' with

grant option;

flush privileges;

创建普通用户：

grant all privileges on *.* to 'myuser'@'%' identified by '123';

创建只读用户：

CREATE USER test IDENTIFIED BY 'Test@123';

grant select on *.* to test@"%" identified by "Test@123";

修改密码：

update mysql.user set authentication_string=password('123')

where user='root' and Host ='localhost';

firewall-cmd --add-port=3306/tcp --permanent

firewall-cmd --reload

主从复制配置：

1、主库上配置my.cnf

server-id=1

port=*3309

user=mysql

log_bin=master-bin

expire_logs_days=30#binlog-do-db=数据库的名字

#binlog-do-db=**_service

#binlog-do-db=**_api

#binlog-ignore-db=mysql

#binlog-ignore-db=information_schema

#binlog-ignore-db=performance_schema

#binlog-ignore-db=sys

重启主库mysql服务systemctl restart mysqld.service

测试log_bin是否成功开启

show variables like '%log_bin%';

2、主库上添加backup用户

grant replication slave on *.* to 'backup'@'%' identified by '1234';

flush privileges;

锁定一下表，保证同步前数据一致，注意锁定只在当前mysql会话有效

flush tables with read lock;

#unlock tables; #slave同步完成后再解锁

查看master状态，记录二进制文件名(master-bin.000002)和位置(591)

show master status;

+-------------------+----------+--------------+------------------+--------------

-----+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+--------------

-----+

| master-bin.000002 | 591 | | |

|

+-------------------+----------+--------------+------------------+--------------

-----+

1 row in set (0.00 sec)

#flush logs; #切换到新的bin文件

3、从库上配置my.cnf

server-id=2replicate_wild_do_table=数据库的名字.%

replicate_wild_do_table=**_service.%

replicate_wild_do_table=**_api.%

replicate_wild_ignore_table=mysql.%

replicate_wild_ignore_table=information_schema.%

replicate_wild_ignore_table=performance_schema.%

replicate_wild_ignore_table=sys.%

重启从库mysql服务systemctl restart mysqld.service

导出主库内容

mysqldump --default-character-set=utf8 --single-transaction --set-gtid-

purged=OFF -hIP -P*3309 -uroot -p*pw -R -E 数据库的名字>

/opt/数据库的名字.sql;

mysqldump --default-character-set=utf8 --single-transaction --set-gtid-

purged=OFF -hIP -P*3309 -uroot -p*pw -R -E **_service>

/opt/**_service.sql;

mysqldump --default-character-set=utf8 --single-transaction --set-gtid-

purged=OFF -hIP -P*3309 -uroot -p*pw -R -E **_api>

/opt/**_api.sql;

导入到从库

CREATE DATABASE `数据库的名字` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE DATABASE `**_service` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE DATABASE `**_api` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

mysql -h127.0.0.1 -P*3309 -uroot -p*pw 数据库的名字 <

/opt/数据库的名字.sql;

mysql -h127.0.0.1 -P*3309 -uroot -p*pw **_service <

/opt/**_service.sql;

mysql -h127.0.0.1 -P*3309 -uroot -p*pw **_api < /opt/**_api.sql;

将从库手动与主库同步之后，在/etc/my.cnf中增加以下内容并重启systemctl restart

mysqld.service

#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错

read_only=1 #可以防止普通权限的用户进行写操作，不能分配all, super权限给普通用户

master_info_repository=TABLE

relay_log_info_repository=TABLErelay_log_recovery=1 #从机禁止写

#super_read_only=1 #从机禁止写 (root也无法进行写操作)

从库上执行sql

CHANGE MASTER TO MASTER_HOST='*IP', MASTER_PORT=*3309,

MASTER_USER='backup', MASTER_PASSWORD='1234', MASTER_LOG_FILE='master-

bin.000001', MASTER_LOG_POS=2156;

启动slave同步线程

start slave;

查看slave状态

show slave status\G;

检查主从复制通信状态

Slave_IO_State: Waiting for master to send event #从站的当前状态

Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行

Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行

Seconds_Behind_Master #是否为0，0就是已经同步了

设置从库只读

set global read_only=1;