mysql

mysql 命令

mysql命令参考。

一些不常用又记不住的命令
#指定ID记录数:
alter table user AUTO_INCREMENT 此处写你想让id从几开始增长的数字

#mysql开放远程连接:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

#刷新权限表，使配置生效:
flush privileges; 

更改表和字段的，字符集。
ALTER TABLE sursen_destruction CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

# 修改数据库字符集
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
ALTER DATABASE sursen_admin DEFAULT CHARACTER SET utf8;
 
# 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
# 如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 只是修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
# 如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 修改字段的字符集：
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
# 如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
 
# 查看数据库编码：
SHOW CREATE DATABASE db_name;
 
# 查看表编码：
SHOW CREATE TABLE tbl_name;
 
# 查看字段编码：
SHOW FULL COLUMNS FROM tbl_name;

ALTER TABLE nimo_finance_merchant_settlement CONVERT TO CHARACTER SET utf8mb4;

ALTER TABLE `sursen-admin`.`sys_users` AUTO_INCREMENT = 100

# 修改数据所有表与表中字段的编码及字符集 分两步
1.利用语句，生成所有实际执行的语句

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="tuser" # 修改为需要更改的数据库名称
AND TABLE_TYPE="BASE TABLE"

# SET \ COLLATE 设置为需要修改为的编码 \ 字符集
# TABLE_SCHEMA="db_name"：修改为数据库名称
# 此语句会基于 MySQL 的元数据表，得到一组可直接执行的 SQL 列表，如下：

ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table3` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table4` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table5` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table6` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 其中，table1 到 table6 即为数据库中的所有数据表。

2.直接将语句粘贴并执行即可。

danger

注意，这里使用 CONVERT TO 而非 DEFAULT，是因为后者不会修改表中字段的编码和字符集
此外，对于数据表比较多的数据库，可以先将第一步的执行结果导出到 .sql 文件，再通过该 SQL 文件执行即可

other
show processlist;show status like '%max_%';show variables like '%max_%';
vi /etc/my.conf
max_connections=5000
innodb_lock_wait_timeout = 500
vi /lib/systemd/system/mysqld.service
LimitNOFILE=65535
LimitNPROC=65535
systemctl daemon-reload
systemctl restart  mysqld.service

DROP USER 'delshards'@'%';
CREATE DATABASE delshards;
CREATE USER 'delshards'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES on delshards.* to 'delshards'@'%'IDENTIFIED by '123456';
update user set password = password ('123456') where user = 'root';
delete from user where user='';
FLUSH PRIVILEGES;

TINYTEXT   最大长度是 255 (2^8 – 1) 个字符。
TEXT       最大长度是 65535 (2^16 – 1) 个字符。
MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。
LONGTEXT   最大长度是 4294967295 (2^32 – 1) 个字符。

Centos7 mysql数据库安装和配置

1.系统环境

yum update升级以后的系统版本为

[root@yl-web yl]# cat /etc/redhat-release 
CentOS Linux release 7.1.1503 (Core)

2.mysql安装

一般网上给出的资料都是
yum install mysql
yum install mysql-server
yum install mysql-devel
//安装mysql和mysql-devel都成功，但是安装mysql-server失败，如下：
[root@yl-web yl]# yum install mysql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.sina.cn
 * extras: mirrors.sina.cn
 * updates: mirrors.sina.cn
No package mysql-server available.
Error: Nothing to do
//查资料发现是CentOS 7 版本将MySQL数据库软件从默认的程序列表中移除，用mariadb代替了

有两种解决办法：

方法一：安装mariadb

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。安装mariadb，大小59 M。

[root@yl-web yl]# yum install mariadb-server mariadb

mariadb数据库的相关命令是：

systemctl start mariadb #启动MariaDB

systemctl stop mariadb #停止MariaDB

systemctl restart mariadb #重启MariaDB

systemctl enable mariadb #设置开机启动

所以先启动数据库

[root@yl-web yl]# systemctl start mariadb

然后就可以正常使用mysql了
[root@yl-web yl]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

##安装mariadb后显示的也是 MariaDB [(none)]> ，可能看起来有点不习惯。下面是第二种方法。

方法二：官网下载安装mysql-server

wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

yum install mysql-community-server

安装成功后重启mysql服务。

 service mysqld restart

初次安装mysql，root账户没有密码。

[root@yl-web yl]# mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql>

设置密码

mysql> set password for 'root'@'localhost' =password('password');
Query OK, 0 rows affected (0.00 sec)

# 创建一个名为testuser的用户：
CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';

# 我们可以使用以下命令来修改用户的密码 newpassword：新密码 username：需要修改密码的用户名
UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE user = 'username';

# 例如，我们想把testuser用户的密码修改为654321，则可以执行以下命令：
UPDATE mysql.user SET authentication_string = PASSWORD('654321') WHERE user = 'testuser';

mysql> 

不需要重启数据库即可生效。在mysql安装过程中如下内容：

Installed:
mysql-community-client.x86_64 0:5.6.26-2.el7
mysql-community-devel.x86_64 0:5.6.26-2.el7
mysql-community-libs.x86_64 0:5.6.26-2.el7
mysql-community-server.x86_64 0:5.6.26-2.el7

Dependency Installed:
mysql-community-common.x86_64 0:5.6.26-2.el7

Replaced:
mariadb.x86_64 1:5.5.41-2.el7_0
mariadb-devel.x86_64 1:5.5.41-2.el7_0
mariadb-libs.x86_64 1:5.5.41-2.el7_0
mariadb-server.x86_64 1:5.5.41-2.el7_0

所以安装完以后mariadb自动就被替换了，将不再生效。

[root@yl-web yl]# rpm -qa |grep mariadb
[root@yl-web yl]#

三、配置mysql

mysql配置文件为/etc/my.cnf 最后加上编码配置

[mysql]
default-character-set =utf8

这里的字符编码必须和/usr/share/mysql/charsets/Index.xml中一致 mysql 这是图片

远程连接设置
## 把在所有数据库的所有表的所有权限赋值给位于所有IP地址的root用户。

mysql> grant all privileges on *.* to root@'%'identified by 'password';

## 如果是新用户而不是root，则要先新建用户

mysql>create user 'username'@'%' identified by 'password'; 

## 此时就可以进行远程连接了。

开启MySQL远程访问权限允许远程连接

mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host         | user | password                                  |
+--------------+------+-------------------------------------------+
| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
2 rows in set (0.00 sec)

update user set host = ’%’ where user = ’root’;

mysql> use mysql;
Database changed
mysql> grant all privileges  on *.* to root@'%' identified by "password";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host         | user | password                                  |
+--------------+------+-------------------------------------------+
| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| %            | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
3 rows in set (0.00 sec)

本地登陆远程阿里云mysql的问题

本地MySQL数据库报SSL connection error: SSL is required but the server doesn't support it. 解决办法不启用SSL，在Advance TAB页，Others框中输入参数：useSSL=0

查看mysql配置文件路径

登陆mysql后执行下面语句
SHOW VARIABLES LIKE 'my%cnf';
+----------------------+----------------------------+
| Variable_name        | Value                      |
+----------------------+----------------------------+
| my_cnf               | /etc/my.cnf                |
| my_ini               | C:\ProgramData\MySQL\My.ini |
+----------------------+----------------------------+

mysql 主从(同步半同步) 主主配置

1.主从配置

docker创建两个mysql

docker run -p 3307:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=root -d f5da8fc4b539
docker run -p 3308:3306 --name mysql_slave  -e MYSQL_ROOT_PASSWORD=root -d f5da8fc4b539

2.开启root用户远程访问

1.登陆mysql 选择对应的库给root开放权限

mysql -h127.0.0.1 -P3307 -uroot -p

use mysql;

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

flush privileges;

3.修改配置文件

主mysql my.cnf
# 1. 必须 指定mysql唯一标识.
server-id=1
# 2. 可选 指定mysql binlog 文件名称.
#log-bin=mysql-bin
# 3. 可选 binlog格式(mixed,statement,row.默认格式是row).
binlog_format=mixed
# 4. 可选 不需要复制的数据库.
#binlog-ignore-db=test 
# 5. 可选 需要复制的数据库.
#binlog-do-db=

从mysql my.cnf
# 1. 必须 指定mysql唯一标识.
server-id=2
# 2. 可选 指定mysql binlog 文件名称.
#log-bin=mysql-bin
# 3. 可选 binlog格式(mixed,statement,row.默认格式是row).
binlog_format=mixed
# 3.5 可选 中继日志
#relay_log=myql-relay-bin
# 4. 可选 不需要复制的数据库.
#binlog-ignore-db=test   
# 5. 可选 需要复制的数据库.
#binlog-do-db=

4.主服务器上创建用户,并且授权.

use mysql;

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;

查看主库状态
show master status;

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     1157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

danger

从现在开始,不要在操作主库了,避免状态值发生变化

5.启用同步,指定主库的信息(从库中执行)

CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_PORT=3307
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1157;

example
CHANGE MASTER TO
MASTER_HOST='172.27.255.187',MASTER_PORT=3307,MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1157;

danger

注意:MASTER_LOG_FILE和MASTER_LOG_POS主服务器保持一致.

start slave;

show slave status\G;

Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 157
               Relay_Log_File: c82ec6c2704f-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 540
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bfd68ae4-7e10-11ef-8f79-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 

tip

都是yes的时候才是成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

6 停止主从同步两个方法

从库操作这么操作

stop slave;

主库操作这么操作

reset master;

mysql 主从半同步复制配置

需要借助插件来完成主服务器和从服务器都需要加载

[mysqld]主服务器和从服务器都需要加载
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

# 在my.cnf 主库和从库中配置
rpl_semi_sync_master_enabled = 1     #表示在master上开启半同步复制模式 1是半同步. 默认是0  主库和从库都配置
rpl_semi_sync_master_timeout = 1000  #表示主库在某次事物中的等待时间为10000毫秒  只有主库配置

SELECT PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGIN WHERE PLUGIN_NAME LIKE '%semi%';

其他配置跟主从异步一样的 如果主库有数据,需要先dump出来导入到从库,在开启主从复制模式

mysql 主主模式

在主库配置文件中也配置上中继日志就可以了 互为从库

Docker 运行 mysql

docker run \
--name qianwen-mysql \
--network qianwen_qianwen.net \
--restart=on-failure:5 \
-e MYSQL_ROOT_PASSWORD=Qianwen@456 \
-p 13306:3306 \
-v ./mysql.cnf:/etc/my.cnf \
-v /www/wwwroot/cicd/qianwen/mysql/data:/var/lib/mysql \
-d registry.cn-beijing.aliyuncs.com/qianwenbj/mysql:8.0

各个项目部署步骤总结

docker 启动 mysql 并配置好配置文件主库
给从库创建用户
导入数据库.为数据拉平做准备
docker 启动 mysql 并配置好配置文件从库
从库导入数据库.数据拉平
从库链接主库 CHANGE MASTER

注意

如果是mysql5.7或以下,在配置server-id的时候要放在配置文件的[mysqld]模块下面.否则不生效.

mysql插入非空字段的问题

线上 mysql 5.6  插入记录 如果有非空字段没有值 而且没有设置默认值 ，插入失败

线下 mysql 5.5  插入记录 如果有非空字段没有值 而且没有设置默认值 ，插入成功

线上  ：

select @@global.sql_mode\G

global.sql_mode : STRICI_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

线下：

select @@global.sql_mode\G

global.sql_mode : NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

最后得出结论

1. 如果sql_mode 没有设置STRICI_TRANS_TABLES，则在插入记录时，如果有非空字段没有值 而且没有设置默认值，则引擎会

自动填充（int类型 填充0  string类型 填充 ''  timestamp 类型填充 当前时间戳（2018-06-22 19:54:52）

2. 标准推荐设置 global.sql_mode : STRICI_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

在mysql配置文件my.cnf 或my.ini 中添加 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

mysql的sql_mode

mysql 命令​

Centos7 mysql数据库安装和配置​

1.系统环境​

2.mysql安装​

方法一：安装mariadb​

方法二：官网下载安装mysql-server​

三、配置mysql​

开启MySQL远程访问权限 允许远程连接​

本地登陆远程阿里云mysql的问题​

查看mysql配置文件路径​

mysql 主从(同步 半同步) 主主 配置​

1.主从配置​

2.开启root用户远程访问​

3.修改配置文件​

4.主服务器上创建用户,并且授权.​

5.启用同步,指定主库的信息(从库中执行)​

6 停止主从同步两个方法​

mysql 主从半同步复制 配置​

mysql 主主模式​

Docker 运行 mysql​

各个项目部署步骤总结​

mysql插入非空字段的问题​

mysql 命令

Centos7 mysql数据库安装和配置

1.系统环境

2.mysql安装

方法一：安装mariadb

方法二：官网下载安装mysql-server

三、配置mysql

开启MySQL远程访问权限允许远程连接

本地登陆远程阿里云mysql的问题

查看mysql配置文件路径

mysql 主从(同步半同步) 主主配置

1.主从配置

2.开启root用户远程访问

3.修改配置文件

4.主服务器上创建用户,并且授权.

5.启用同步,指定主库的信息(从库中执行)

6 停止主从同步两个方法

mysql 主从半同步复制配置

mysql 主主模式

Docker 运行 mysql

各个项目部署步骤总结

mysql插入非空字段的问题