首页 > 代码库 > 学习笔记--mysql5.7新版本
学习笔记--mysql5.7新版本
mysql5.7
在redhat6上安装新版本的mysql，若存在旧版本的mysql应卸载掉
mysql —version 查看mysql版本
rpm -qa | grep mysql 查看已经安装的mysql(需卸载的mysql包)
新版本安装包：mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
解压并安装：yum install -y mysql-community-*
安装成功后启动mysql服务：/etc/init.d/mysqld start
执行mysql_secure_installation进行安全初始化向导
[root@server1 mnt]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
这里提示输入root密码，这里与以往版本不同，新版本的密码此时已经生成，在/var/log/mysqld.log中查询temporary关键字：
2016-08-21T14:27:46.740332Z 1 [Note] A temporary password is generated for root@localhost: gw3;B:fDuJ?A
gw3;B:fDuJ?A即为初始密码，输入密码进行下一步
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
####是否删除匿名用户,生产环境建议删除
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
####是否禁止root远程登录,根据自己的需求选择Y/n
Remove test database and access to it? (Press y|Y for Yes, any other key for No)
####是否删除test数据库
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :
####是否重新加载权限表
提示All done!配置完成
AB复制
环境：server1.example.com 172.25.254.1
server2.example.com 172.25.254.2
将server1作为主服务器，server2作为从服务器,同步库之前需对该库进行数据手动同步
在server1上编辑mysql配置文件/etc/my.cnf添加以下参数：
log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步 westos库,再添加一行“binlog-do-db=westos”,以此类推
server-id=1#必须为 1 到 232–1 之间的一个正整数值
gtid-mode=ON #启动GTID
enforce-gtid-consistency=on
官方文档介绍：
该特性最大的用处就是，如果我们的备库只是作为只读节点，那么就可以关闭复制线程的binlog来降低复制的开销(关闭 log_slave_updates)，同时还会维持SQL线程拥有的事务GTID到系统表中。这样即使主备切换，我们也不会丢失GTID。而在之前的版 本中，是不允许在打开GTID时关闭log_slave_updates的。
当然了，如果你的备库是级联的一部分，也就是说，是另外一台备库的主库，这个特性就发挥不了作用啦。
在server2上编辑mysql配置文件/etc/my.cnf添加以下参数：
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
在server1上
mysql> grant replication slave on *.* to ‘yml‘@‘172.25.254.2‘ identified by ‘Yml123456!@#‘;
# 授予用户权限用于主从同步
mysql>change master to master_host=‘172.25.254.1‘,master_user=‘yml‘,master_password=‘Yml123456!@#‘,master_auto_position=1
#在server2上启动从数据库，添加主DB server指向 自动寻找position号
mysql> start slave;
mysql> show slave status\G;
测试：
在主服务端对test库进行操作，并在从服务端查看是否同步，同时查看从服务端的mysql库中的gitd表
mysql> select * from gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 67066110-67ab-11e6-a71d-525400237b2e | 1 | 1 |
+--------------------------------------+----------------+--------------+
开启并行复制
在从服务端配置文件中添加以下：
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 ####开启并行复制，线程为16
master_info_repository=TABLE
relay_log_info_repository=TABLE ##这两条为官方推荐，能提升百分之50的性能
重启服务
mysql> show processlist;
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
| 23 | system user | | NULL | Connect | 25040 | Waiting for master to send event | NULL |
| 24 | system user | | NULL | Connect | 25039 | Slave has read all relay log; waiting for more updates | NULL |
| 25 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 26 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 27 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 28 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 29 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 30 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 31 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 32 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 33 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 34 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 35 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 36 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 37 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 38 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 39 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 40 | system user | | NULL | Connect | 25040 | Waiting for an event from Coordinator | NULL |
| 42 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
此时多线程已经成功开启
学习笔记--mysql5.7新版本