mysql 5.7 sharding_java架构之路（Sharding JDBC）mysql5.7yum安装和主从

安装mysql5.7单机

1.获取安装yum包

[root@iZm5e7sz135n16ua2rmbk6Z local]#wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

你可以下载到你的本地，然后上传到linux某目录下

2.安装软件源，在其目录下输入

[root@iZm5e7sz135n16ua2rmbk6Z local]#rpm -Uvh mysql57-community-release-el7-11.noarch.rpm

3.安装mysql服务端

[root@iZm5e7sz135n16ua2rmbk6Z local]#yum install -y mysql-community-server

4.启动mysql

[root@iZm5e7sz135n16ua2rmbk6Z local]#service mysqld start

Redirecting to /bin/systemctl start mysqld.service

5.查看mysql运行状态

[root@iZm5e7sz135n16ua2rmbk6Z local]#service mysqld status

Redirecting to /bin/systemctl status mysqld.service

● mysqld.service-MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since Sun2019-11-24 14:45:59CST; 20s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process:14787 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

Process:14667 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID:14791(mysqld)

CGroup:/system.slice/mysqld.service

└─14791 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Nov24 14:45:55 iZm5e7sz135n16ua2rmbk6Z systemd[1]: Starting MySQL Server...

Nov24 14:45:59 iZm5e7sz135n16ua2rmbk6Z systemd[1]: Started MySQL Server.

6.初始化密码

[root@iZm5e7sz135n16ua2rmbk6Z local]#grep 'temporary password' /var/log/mysqld.log

2019-11-24T06:45:56.831037Z 1 [Note] A temporary password is generated for root@localhost: -cTy=VNwI7!H

[root@iZm5e7sz135n16ua2rmbk6Z local]#

每个人都不一样，别复制我的。输入mysql -uroot -p，然后输入你的密码进入

[root@iZm5e7sz135n16ua2rmbk6Z local]#mysql -uroot -p

Enter password: -cTy=VNwI7!H

Welcome to the MySQL monitor. Commands end with ;org.

Your MySQL connection idis 2Server version:5.7.28Copyright (c)2000, 2019, Oracle and/orits affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

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> alter user 'root'@'localhost' identified by '%liX9-k

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';//查看密码策略+--------------------------------------+--------+

| Variable_name | Value |

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

| validate_password_check_user_name | OFF |

| validate_password_dictionary_file | |

| validate_password_length | 8 |

| validate_password_mixed_case_count | 1 |

| validate_password_number_count | 1 |

| validate_password_policy | MEDIUM |

| validate_password_special_char_count | 1 |

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

7 rows in set (0.00sec)

mysql>

mysql> set global validate_password_policy=LOW;//修改限制策略

Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;//修改密码长度

Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123456';//修改一个简单的密码(练习方便,实际不建议这样做)

Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.user set host = '%' where user = 'root';//允许远程访问DB

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0

mysql>FLUSH PRIVILEGES;//刷新权限

Query OK, 0 rows affected (0.00 sec)

到这里就可以了,反正我是按照上面的代码复制粘贴完全可以成功,不知道你们可以吗?

mysql的主从配置

这里我给大家搭建一个简单的主从(从)配置,就是说一个主节点对应一个从节点,那个从节点下还有一个从节点.

1.准备三台服务器,分别安装一下mysql,按照上面的按照就可以的.

2.编辑master配置文件

[root@iZm5eitsswnra4j66dzb9nZ ~]#vim /etc/my.cnf

log-bin=/usr/local/mysql/data/binlog/mysql_bin #打开Mysql日志，日志格式为二进制 mysq_bin结尾是固定的别改，前面是路径

server-id=1 #[必须]服务器唯一ID，默认是1

sync-binlog=1 #每次执行写入就与硬盘同步

binlog-do-db=xiaocaijishu #需要同步的二进制数据库名

expire-logs-days=7 #只保留7天的二进制日志，以防磁盘被日志占满

binlog-ignore-db=information_schema #不备份的数据库

binlog-ignore-db=performation_schema #不备份的数据库

binlog-ignore-db=sys #不备份的数据库

重启mysql-master，可能会启动失败，是由于mysql的binlog的用户组不对，再来改一下用户组，以我们的/usr/local/mysql/data/binlog为例

[root@iZbp1hvg9qhaf4c75o2k58Z binlog]#chown -R mysql /usr/local/mysql/data/binlog/

[root@iZbp1hvg9qhaf4c75o2k58Z binlog]#chgrp -R mysql /usr/local/mysql/data/binlog/

3.设置一个同步账号

mysql> CREATE USER 'xiaocaijishu'@'47.105.%.%' IDENTIFIED BY '123456'; // 设置账号xiaocaijishu用于47.105等IP地址的mysql同步,你可以直接写%全匹配

Query OK, 0 rows affected (0.00sec)

mysql> grant replication slave,replication client on *.* to xiaocaijishu@'47.105.%.%' identified by 'root';//赋予权限

Query OK, 0 rows affected,1 warning (0.00sec)

mysql>FLUSH PRIVILEGES; //刷新权限

Query OK, 0 rows affected (0.00 sec)

4.设置slave节点配置文件

server-id=2 #[必须]服务器唯一ID，默认是1relay_log= /usr/local/mysql/data/binlog/mysql_relay_bin #开启中继日志

read_only = 1 #设置只读权限

log_bin=/usr/local/mysql/data/binlog/mysql_bin #开启binlog，如果没有下级节点无须开启。

log_slave_updates = 1 #使得更新的数据写进二进制日志中

replicate-rewrite-db = xiaocaijishu -> xiaocai #从主节点的xiaocaijishu库写入到从节点的xiaocai库#replicate-wild-do-table 指定需要同步的表。

5.查找主节点日志

mysql>show master status;+------------------+----------+--------------+--------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql_bin.000001 | 1349 | xiaocaijishu | information_schema,performation_schema,sys | |

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

1 row in set (0.00 sec)

6.创建主从连接

首先我们进入进入mysql客户端，然后输入

mysql> change master to master_host='121.41.60.188',master_user='xiaocaijishu',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=1349;

Query OK, 0 rows affected,2 warnings (0.01 sec)

意思是master_host=“我们的主节点IP”，master_user=“刚才创建的主节点用户”，master_password=“账户密码”，master_log_file=“需要从哪个二进制binlog复制”，master_log_pos=“对应主节点的Position”

如遇报错请先输入

mysql>stop slave;

Query OK, 0 rows affected (0.00 sec)

然后输入start slave开启线程

mysql>start slave;

Query OK, 0 rows affected (0.00 sec)

输入SHOW SLAVE STATUSG检查线程是否创建成功

如果看到上面的那个Yes，就说明主从搭建完成了，如果下面还有搭建从节点，开启binlog，和上面一个道理。

如果遇到

Last_Errno: 1051

Last_Error: Error 'Unknown table'xiaocai.student'' on query. Default database: 'xiaocai'. Query: 'DROP TABLE `student` /* generated by server */'

可以尝试下面的解决方式来处理。

mysql>stop slave;

Query OK, 0 rows affected (0.01sec)

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Query OK, 0 rows affected (0.00sec)

mysql>start slave;

Query OK, 0 rows affected (0.01 sec)