1.下载 mysql 源安装包html
$ curl -LO http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
2.安装 mysql 源python
$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm
注意:执行过程当中若是报错以下mysql
则经过修改python版本, 修改yum配置文件,将python版本指向之前的旧版本sql
# 修改yum配置文件,将python版本指向之前的旧版本 # vi /usr/bin/yum #!/usr/bin/python2.7 # 修改urlgrabber-ext-down文件,更改python版本 # vi /usr/libexec/urlgrabber-ext-down #!/usr/bin/python2.7
检查 yum 源是否安装成功数据库
$ sudo yum repolist enabled|grep "mysql.*-community.*" mysql-connectors-community/x86_64 MySQL Connectors Community 95 mysql-tools-community/x86_64 MySQL Tools Community 84 mysql57-community/x86_64 MySQL 5.7 Community Server 327
3.安装server安全
$ sudo yum install mysql-community-server
4.启动mysql服务服务器
安装服务网络
$ sudo systemctl enable mysqld
启动服务less
$ sudo systemctl start mysqld
查看服务状态python2.7
$ sudo systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 一 2019-03-04 16:36:09 CST; 17s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 3126 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 3053 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 3129 (mysqld) Tasks: 27 CGroup: /system.slice/mysqld.service └─3129 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid 3月 04 16:36:02 lvmama02 systemd[1]: Starting MySQL Server... 3月 04 16:36:09 lvmama02 systemd[1]: Started MySQL Server.
5.修改默认密码
MySQL 5.7启动后,在 /var/log/mysqld.log 文件中给 root 生成了一个默认密码。经过下面的方式找到 root 默认密码,而后登陆 mysql 进行修改
$ grep 'password' /var/log/mysqld.log 2019-03-04T08:36:04.854935Z 1 [Note] A temporary password is generated for root@localhost: pdU7wuS/YhSG
登陆mysql修改密码
#注意:MySQL 5.7 默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包 #含:大小写字母、数字和特殊符号,而且长度不能少于 8 位。 $ ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxx'
注意:
测试环境咱们能够设置禁用密码校验,毕竟密码太难记了,能够经过以下方式禁用: $ sudo vi /etc/my.cnf # 添加以下配置 # 禁用密码校验策略 validate_password = off # 从新启动mysql 从新修改密码 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'
6.添加远程登陆用户
# MySQL 默认只容许 root 账户在本地登陆,若是要在其它机器上链接 MySQL,必须修改 root 容许远程链接, # 或者添加一个容许远程链接的账户,为了安全起见,本例添加一个新的账户: mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec)
7.配置默认编码为 utf8
MySQL 默认为 latin1, 通常修改成 UTF-8
$ vi /etc/my.cnf [mysqld] # 在myslqd下添加以下键值对 character_set_server=utf8 init_connect='SET NAMES utf8'
重启测试查看字符集
mysql> show variables like 'character%' +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
8.开启端口 (若是防火墙没关闭则须要作以下操做)
$ sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent FirewallD is not running $ sudo firewall-cmd --reload FirewallD is not running
配置主库
1,主库 mysql11 上开启设置
# 添加以下配置 # 参数必须惟一, 本例主库设置为 11 ,从库设置为 12 server_id=101 log_bin=/var/log/mysql/mysql-bin
2,二进制日志文件的目录不是默认的,须要新建一下
# 建立文件夹 $ sudo mkdir /var/log/mysql # 分配权限 $ sudo chown mysql:mysql /var/log/mysql
3,重启主库的 MySQL 服务
$ sudo systemctl restart mysqld
4,测试
mysql> show master status +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+
从结果看到, File 字段有值,而且前面与配置文件一致,说明配置正确。后面的 000001 说明是第一次,若是 MySQL 重启服务,这个值会递增为 mysql-bin.000002
配置从库
1,从库 mysql11 上配置
# 从库配置 server_id=12 log_bin=/var/log/mysql/mysql-bin.log relay_log=/var/log/mysql/mysql-relay-bin.log #库设为只读的 read_only=1
2,从库设置的二进制日志文件的目录不是默认的,须要新建一下
$ sudo mkdir /var/log/mysql # 分配权限 $ sudo chown mysql:mysql /var/log/mysql
3,重启从库的 MySQL 服务
$ sudo systemctl restart mysqld
4,设置从库的复制参数
mysql> CHANGE MASTER TO MASTER_HOST='192.168.187.11', -> MASTER_USER='admin', -> MASTER_PASSWORD='admin', #此选项初始化设置时须要跟主库中的一致。设置好后,若是主 #库发生重启等,不需再次设置,从库会跟着更新 -> MASTER_LOG_FILE='mysql-bin.000001', # master Position的值 -> MASTER_LOG_POS=154;
5.查看从库状态
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.187.11 Master_User: admin Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No *************************** 略 ***************************
6,从 Slave_IO_State, Slave_IO_Running: No, Slave_SQL_Running: No 代表当前从库的复制服务尚未启动,启动从库
mysql> start slave; Query OK, 0 rows affected (0.03 sec)
再次查看show slave status G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.187.11 Master_User: admin Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes *************************** 略 ***************************
7,测试在主库中新建一个库,查看从库是否同步复制主库数据
# ---------mater-------- mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.02 sec) mysql> create database test; Query OK, 1 row affected (0.02 sec) # ---------slave-------- mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.01 sec)
基于语句复制(STATEMENT)
基于行复制(ROW)
常见event以下:
咱们也能够经过binlog 看到这些事件,经过mysql提供的工具查看binlog日志,以下:
MySQL的复制(replication)功能配置简单,深受开发人员的喜欢,基于复制的读写分离方案也很是流行。而MySQL数据库高可用大 多也是基于复制技术,可是MySQL复制自己依然存在部分缺陷,最为主要的问题以下:
从MySQL 5.7的lossless semi-sync replication已经解决了主从数据丢失的问题,MySQL 5.7的multi-thread slave也很大程度地解决了数据同步延迟的问题,MySQL 5.7的Group replication也很大程度地解决了扩展性问题。另外,MySQL 5.7.22 backlog了MySQL 8.0中的基于WriteSet的并行复制,能够说彻底解决了主从数据延迟的问题。能够看出,MySQL正在朝着一个很是好的方向发展