ubuntu系统 - 数据库 mysql命令 5.7 mysql常用命令 - 持续更新版 含mysql主从

一  初始安装和基础

首先删除mysql:
sudo apt-get remove mysql-*
然后清理残留的数据
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
它会跳出一个对话框，你选择yes就好了
然后安装mysql

sudo apt-get install mysql-client mysql-server  libmysqlclient-dev

sudo apt-get install libmysqlclient-dev python3-dev

安装的时候会提示要设置root密码，如果你没有在卸载的时候去清理残留数据是不会提示你去设置root密码的
检查mysql是不是在运行
sudo service mysql status
一般安装完成之后都是会自动运行的。
如果没有运行你可以
sudo service mysql start
运行它

#注意  mysql5.7  的 配置文件：

 /etc/mysql/mysql.conf.d/mysqld.cnf 

需要远程链接需要注释掉bind-address = 127.0.0.1： 

保存退出，然后进入mysql服务，可以创建一个本地用户可以修改密码执行授权命令：

然后执行quit命令退出mysql服务，执行如下命令重启mysql：

sudo service mysql restart

二 常用命令概览

# 密码强度修改

set global validate_password_policy=0;  # 密码强度
set global validate_password_length=1; # 密码强度
# 确认密码强度策略 
SHOW VARIABLES LIKE "%password%";

 +----------------------------------------+-----------------+
| Variable_name                          | Value           |
+----------------------------------------+-----------------+
| default_password_lifetime              | 0               |
| disconnect_on_expired_password         | ON              |
| log_builtin_as_identified_by_password  | OFF             |
| mysql_native_password_proxy_users      | OFF             |
| old_passwords                          | 0               |
| report_password                        |                 |
| sha256_password_auto_generate_rsa_keys | ON              |
| sha256_password_private_key_path       | private_key.pem |
| sha256_password_proxy_users            | OFF             |
| sha256_password_public_key_path        | public_key.pem  |
| validate_password_check_user_name      | OFF             |
| validate_password_dictionary_file      |                 |
| validate_password_length               | 4               |   # 长度已经修改 原来是8
| validate_password_mixed_case_count     | 1               |
| validate_password_number_count         | 1               |
| validate_password_policy               | LOW             |  # 强度从变成了 LOW  原来是 MEDIUM
| validate_password_special_char_count   | 1               |
+----------------------------------------+-----------------+

grant all on *.* to mylocal@localhost identified by 'mylocal' with grant option;
flush privileges;

#刷新权限

flush privileges;

#创建utf-8     或   uft8mb4数据库

CREATE DATABASE `dbname` CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE  `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE  `dbname` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#查看当前用户
select user();

#查看某个用户的详细信息：
show grants for '用户名'@'%'; 
show grants for 'zabbix'@'%';
show grants for 'zabbixproxy01'@'%';

# ---------------------------创建一个 confluence  和一个 本地和远程都可以登陆的用户confluence   密码 Xiandai@2020 数据库 并设置隔离级别 

# 注意远程访问还需要配置一下my.cnf（查看此文件内容里是否提到其他配置文件） 增加一行： 

bind-address=0.0.0.0

# 创建一个用户confluence  可以拥有confluence 所有权限并且可以本地和远程都可以访问 密码 Xiandai@2020
 

create database confluence character set utf8 collate utf8_bin;
grant all privileges on confluence.* to confluence@'%' identified by 'Xiandai@2020'; 

# fanqianghui 要我设置他这个库的数据库隔离级别（不懂啥意思）

SET GLOBAL tx_isolation='READ-COMMITTED';

# -------------------------zabbix常用 创建一个本地用户zabbix 拥有zabbix的库的所有权限 密码password

create database zabbix character set utf8 collate utf8_bin;
grant all privileges on zabbix.* to zabbix@localhost identified by 'password';

grant all on *zabbix_proxy.* to 'zabbix'@'%' identified by 'zabbix';    # 这一条是远程可以访问 命令格式还不一样 

#-------------------添加一个用户admin 访问所有all库并授权可从任何其它主机发起的访问（通配符％）。使用这一条语句即可。慎用！推荐上面两个
 

grant all privileges on *.* to admin@"%" identified by 'password' with grant option;

# 查询用户列表
 

select user,host from mysql.user;

#查看表结构
 

desc mysql.user;

#查看 存放目录

show variables where Variable_name = 'datadir';

三、设置mysql远程访问

1. 编辑mysql配置文件，把其中bind-address = 127.0.0.1注释了

1	vi /etc/mysql/mysql.conf.d/mysqld.cnf

2. 使用root进入mysql命令行，执行如下2个命令，示例中mysql的root账号密码：root

grant all on *.* to root@'%' identified by 'root' with grant option;

flush privileges;

3. 重启mysql

/etc/init.d/mysql restart

给用户授权‘PRIVILEGES’   可以换成 insert delete 等分别授权，此命令也可以用于修改用户权限

例资 给用户给new 密码123456  授权 PRIVILEGES

示例:

GRANT ALL PRIVILEGES ON *.* TO 'new'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

执行完后,再刷新一下权限就可以了,不用重启   重启成功后，在其他计算机上，便可以远程登录

flush privileges;

四 MySQL主从

分三步 

1  主库文件备份到从库一份（从库没有的表需要创建一下 ）

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

说明  ： -u ：用户名   -p ：示密码    --all-databases ：所有数据库

--lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改

~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定

#从库执行备份sql

mysql –uroot –pmysql < master_db.sql

2 设置 主库的id  开启binlog 二进制日志和配置主从库的id   默认备份所有库 有需要可以修改db选项备份某个库

vim /etc/mysql/mysql.conf.d/mysqld.cnf

 修改配置主库修改两项 （从库修改为2 或者其他数字 修改 server-id此一项即可）

83 server-id               = 1 
 84 log_bin                 = /var/log/mysql/mysql-bin.log

#记得重启MySQL

service mysql restart

3 创建slave账号 ip   

# 从库配置 salve账号和登录和相关  ip --  账号密码-- master File --Position 的status  一个都不能错 上代码：

#主库

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
FLUSH PRIVILEGES;


SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    39504 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#  从库  看见两个 YES 就ok 

# 有错误就检查 ID  用户名  密码  ；master_log_file='mysql-bin.000006', master_log_pos=39054

#（备注 有时候做实验的时候，是UUID一样 记得去 cd /var/lib/mysql/   vi auto.cnf  /修改server-uuid 即可）

change master to master_host='192.168.0.111', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=39054;



show slave status \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.111
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 39504
               Relay_Log_File: ubuntu-relay-bin.000004
                Relay_Log_Pos: 33776
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
略... 

五   连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

• 内连接查询：查询的结果为两个表匹配到的数据

• 

• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

  

• 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

  

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

例1：使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id = classes.id;

例2：使用左连接查询班级表与学生表

• 此处使用了as为表起别名，目的是编写简单

select * from students as s left join classes as c on s.cls_id = c.id;

例3：使用右连接查询班级表与学生表

select * from students as s right join classes as c on s.cls_id = c.id;

例4：查询学生姓名及班级名称

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

 总结查询关键词先后顺序（详情请见 HM18年python）：

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]