Install & Config

Linux yum源

repo.mysql.com/yum/

配置扩展源

rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm

yum install mysql

yum install mysql-community-server -y

启动mysql、加入开机自启

centos 7

systemctl start mysqld
systemctl stop mysqld
systemctl enable mysqld

使用mysql初始密码登录数据库

修改数据库密码

修改默认安全策略级别

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.01 sec)

mysql> set password for root@localhost = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[delores@localhost ~]$ mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

设置远程访问

mysql> grant all on *.* to root@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

源码安装

Docker install mysql image

Mac install mysql

主从复制 + 读写分离

主从复制又称为AB复制

传统的mysql复制就是主-从复制,它会有一个主,一个或多个从,在主节点提交与执行完事物之后,通过bin-log(2进制日志),将其(异步的)通过从节点上的IO线程发送到从节点上，并将bin-log日志存到从节点的readly-log(中继日志)中，通过sql线程以重新执行(在基于语句的复制中),或应用(在基于行的复制中), 当从节点连上主节点后,会向主节点发送上次同步的位置,主节点将日志文件发给从节点

读写分离是建立在主从复制的基础上！！！

配置

Master节点配置

/etc/my.cnf

Slave节点配置

授予slave服务器可以同步master

grant replication slave, replication client on *.* to 'root'@'172.16.157.101(slave IP)' identified by 'root(slave pwd)'

mysql> grant replication slave, replication client on *.* to 'root'@'172.16.157.101' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| root          | %              |
| root          | 172.16.157.101 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

mysql>

查看master状态

show master status;

slave进行关联master节点

进入到slave节点
开始绑定 change master to master_host='172.16.157.100', master_user='root', master_password='root', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=2079

启动主从复制

slave start slave

查看主从同步状态

show slave status\G

问题排查

由于镜像复制，mysql uuid 相同，导致启动失败

[delores@localhost ~]$ sudo find / -name 'auto.cnf'
[sudo] password for delores:
/var/lib/mysql/auto.cnf
[delores@localhost ~]$ sudo vim /var/lib/mysql/auto.cnf

修改一个格式正确的uuid 
重启
slave: change master to .....
slave: start slave

MySQL