MySQL主从数据库

## Centos7 安装mysql

MySQL官网下载地址

http://dev.mysql.com/downloads/mysql/

### Mysql安装

```

(1）# rpm -qa | grep mariadb  

卸载mariadb

(2)   # rpm -e --nodeps mariadb-libs  

将下载的安装包拷贝到centos中解压

(3)   # tar -xf mysql-5.7.28-1.el6.x86_64.rpm-bundle.tar

在安装目录下执行rpm安装

# rpm -ivh mysql-community-common-5.7.28-1.el6.x86_64.rpm

# rpm -ivh mysql-community-libs-5.7.28-1.el6.x86_64.rpm

# rpm -ivh mysql-community-client-5.7.28-1.el6.x86_64.rpm

# rpm -ivh mysql-community-server-5.7.28-1.el6.x86_64.rpm

### 可能遇到的其他依赖和问题

# yum install net-tools -y

# yum install perl -y

安装server可能遇到 检查签名

解决办法

# rpm -ivh mysql-community-server-5.7.28-1.el6.x86_64.rpm --force --nodeps

修改/etc/my.cnf文件,在[mysqld]节点下添加如下配置

[mysqld]

explicit_defaults_for_timestamp=true //显示指定默认值为timestamp类型的字段

查看datadir的值：

datadir=/var/lib/mysql

初始化数据库

# mysqld --initialize --user=mysql

查看生成的临时密码

# cat /var/log/mysqld.log  

启动mysql服务

# service mysqld start

登录mysql

# mysql -uroot -p     （输入临时密码）

修改root用户密码

mysql> set password = password("000000");

查看字符编码

```mysql

show variables like "%char%";

永久修改字符编码

[client]

default-character-set=utf8

[mysql]

character_set_server=utf8

collation-server=utf8_general_ci

修改已有库和表的编码

mysql> alter database test character set 'utf8';

Query OK, 1 row affected (0.01 sec)

mysql> alter table testconvert to character set 'utf8';

Query OK, 1 row affected (0.09 sec)

Records: 1 Duplicates: 0 Warnings: 0

### MySQL的用户管理

（1）MySQL的用户管理在 mysql库中的user表中

  需要了解的列: Host,User, authentication_string等， 可通过 desc user 查看user表结构

（1）相关命令

| 命令                                                         | 描述                                                         | 备注                                                         |

| ------------------------------------------------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ |

| create user zhang3 identified by '123123';                   | 创建名称为zhang3的用户，密码设为123123；                     |                                                              |

| select host,user,password,select_priv,insert_priv,drop_priv  from mysql.user; | 查看用户和权限的相关信息                                     |                                                              |

| set password  =password('123456')                            | 修改当前用户的密码                                           |                                                              |

| update  mysql.user set authentication_string=password('123456') where user='li4'; | 修改其他用户的密码  注意:mysql 5.7 通过authentication_string表示密码列 | 所有通过user表的修改，必须用flush privileges;命令才能生效    |

| update  mysql.user set user='li4' where user='wang5';        | 修改用户名                                                   | 所有通过user表的修改，必须用flush privileges;命令才能生效    |

| drop  user li4                                               | 删除用户                                                     | 不要通过delete from user  u where user='li4' 进行删除，系统会有残留信息保留。 |

修改用户密码

update mysql.user set authentication_string=password('123456') where user='li4';

注意：所有通过user表的修改，必须用flush privileges;命令才能生效

远程工具访问

当前root用户对应的host值为localhost，意味着只允许本机连接

mysql> update user set host='%' where user = 'root';

需要将host的值修改为%，表示允许所有远程通过 TCP方式的连接

### MYSQL的权限管理

#### 授予权限

| 命令                                                         | 描述                                                         |

| ------------------------------------------------------------ | ------------------------------------------------------------ |

| grant 权限1,权限2,…权限n  on 数据库名称.表名称 to  用户名@用户地址 identified by ‘连接口令’ | 该权限如果发现没有该用户，则会直接新建一个用户。  示例：  grant select,insert,delete,drop on atguigudb.* to  li4@localhost ;  给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。 |

| grant all privileges on *.* to joe@'%' identified by '123';  | 授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123. |

####  收回权限

| 命令                                                         | 描述                                | 备注 |

| ------------------------------------------------------------ | ----------------------------------- | ---- |

| show grants                                                  | 查看当前用户权限                    |      |

| revoke [权限1,权限2,…权限n]  on   库名.表名   from 用户名@用户地址 ; | 收回权限命令                        |      |

| REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;         | 收回全库全表的所有权限              |      |

| REVOKE select,insert,update,delete ON mysql.* FROM  joe@localhost; | 收回mysql库下的所有表的插删改查权限 |      |

提示：权限收回后，必须用户重新登录后，才能生效。

### 主从复制

#### 主从复制基本原理


> 1)    master将改变记录到二进制日志（binary log）。

> 这些记录过程叫做二进制日志事件，binary log events；

> 不管使用何种存储引擎，在server层都可以开启binlog日志功能。binlog会记录所有的逻辑操作，并且是采取追加写的形式，将写操作命令，记录在一个二进制文件中。因此binlog日志通常用于恢复数据，或者是主从复制。

> 2)    slave将master的binary log events拷贝到它的中继日志（relay log）；  

> 3)    slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

#### 复制的基本原则

1)   每个slave只有一个master

2)   每个slave只能有一个唯一的服务器ID

3)   每个master可以有多个salve

#### 一主(hadoop01)一从(hadoop02)

1)    修改主机hadoop01的 my.cnf配置文件

server-id=1   #server实例的id

log-bin=/var/lib/mysql/mysql-bin  #log-bin文件存储位置

# mysql-bin就可以了

binlog_format=ROW  # 设置log-bin格式 STATEMENT  ROW  MIXED  

\#可选的配置

binlog-ignore-db=mysql # 设置不要复制的数据库

binlog-do-db=xxx # 设置需要复制的主数据库名字

注意:修改完后重启MySQL服务

2)    修改从机hadoop02的my.cnf 配置文件

server-id=2  #server实例的id

relay-log=mysql-relay  #中继日志

3)    在主机hadoop01上建立账户并授权从机slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'从机器数据库IP' IDENTIFIED BY '密码';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.35.202.103' IDENTIFIED BY '000000';

4)    查询注解hadoop01 的状态

mysql> show master status ;

注意:记录下File 和Position的值，切记不要再操作主机MySQL，防止主机的File和Position  

  值发生变化

5)    在从机hadoop02上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='主机IP',

   MASTER_USER='主机创建好的用户',

   MASTER_PASSWORD='密码',

   MASTER_LOG_FILE='File名1字',

   MASTER_LOG_POS=Position数字;

mysql> CHANGE MASTER TO MASTER_HOST='10.35.202.102',

   MASTER_USER='slave',

   MASTER_PASSWORD='000000',

   MASTER_LOG_FILE='mysql-bin.000002',

   MASTER_LOG_POS=450;

6)    在从机hadoop101上启动主从复制功能

mysql> start slave;

7)    在从机hadoop101查看主从复制状态

mysql> show slave status\G;

主要查看两个参数:  

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

8)    测试主从

在主机hadoop100上新建库、新建表、插入数据，查看从机是否复制

9)    在从机hadoop101停止主从

mysql> stop slave ;