?serverTimezone=GMT

constraint fk_stuinfo_major  foreign key   (majorId) references  major (id)
foreign key  (majorId) references major (id)

create database db_example;
#username=localhost
#password=ThePassword
create user 'springuser'@'localhost' identified by 'ThePassword';
grant all on db_example.* to 'springuser'@'localhost';

set autocommit =0;
insert into book(bid, bname, price, btypeId) VALUES (1,'小李飞刀',100,5);
#(回滚了不插入)
rollback ;

CREATE TABLE `students`.`stu` (
  `stuID` VARCHAR(12) NOT NULL,
  `stuName` VARCHAR(20)  DEFAULT NULL,
  `stuSex` VARCHAR(4)  DEFAULT NULL,
  `stuAge` VARCHAR(11)  DEFAULT NULL,
  `stuJG` VARCHAR(10)  DEFAULT NULL,
  `stuDept` VARCHAR(30)  DEFAULT NULL,
  PRIMARY KEY (`stuID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'stuID';

CREATE TABLE `students`.`t_student` (
  `studID` VARCHAR(15) NOT NULL,
  `stuName` VARCHAR(10) NOT NULL,
  `age` INT NOT NULL,
  `sex` VARCHAR(2) NOT NULL,
  `birth` DATETIME NULL,
  PRIMARY KEY (`studID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE `students`.`t_result` (
  `stuID` VARCHAR(15) NOT NULL,
  `curID` VARCHAR(15) NOT NULL,
  `result` DOUBLE NULL,
  PRIMARY KEY (`stuID`, `curID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

create database crmdb;
use crmdb;
CREATE TABLE `crmdb`.`customer` (
  `CNO` VARCHAR(20) NOT NULL COMMENT '编号',
  `Name` VARCHAR(20) NOT NULL COMMENT '姓名',
  `Sex` VARCHAR(4) NOT NULL COMMENT '性别',
  `Principalship` VARCHAR(10) NULL COMMENT '职务',
  `Company` VARCHAR(40) NULL COMMENT '公司/单位',
  `Telephone` VARCHAR(20) NULL COMMENT '公司电话',
  `Address` VARCHAR(40) NULL COMMENT '公司地址',
  `Background` VARCHAR(80) NULL COMMENT '公司背景',
  PRIMARY KEY (`CNO`))	
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO `crmdb`.`customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2008002',
'郭华',
'女',
'董事长',
'光华集团',
'8221089',
'深圳',
'上市公司');

INSERT INTO `crmdb`.`customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2008001',
'江中',
'女',
'董事长',
'时代在线',
'8372168',
'长沙',
'上市公司');

INSERT INTO `crmdb`.`customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2007002',
'李兴',
'男',
'经理',
'九州方圆',
'6182755',
'北京',
'上市公司');

INSERT INTO `crmdb`.`customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2007001',
'胡振',
'男',
'总经理',
'华夏大邦',
'8226858',
'上海',
'上市公司');

select * from customer;

DROP DATABASE `crmdb`;

UPDATE `crmdb`.`customer`
SET
`Telephone` = '021-8226859'
WHERE `Name` = '胡振';
select * from customer where Company='时代在线';
select * from customer where Sex='男';
DELETE FROM `crmdb`.`customer`
WHERE CNO='2007002';

CREATE TABLE `crmdb`.`dbphoto` (
  `id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NULL,
  `description` VARCHAR(200) NULL,
  `photo image` VARCHAR(45) NOT NULL,
  `photo mediumblob` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

org.hibernate.dialect.MySQLDialect

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/crmdb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
username=root
password=123456

[root@localhost ~]#  mysql -uroot -p123456
mysql> create database yidiankt;
Query OK, 1 row affected (0.65 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yidiankt           |
+--------------------+
5 rows in set (0.26 sec)
mysql> 

[root@localhost ~]#  mysql -uroot -p123456
mysql> create database yidiankt;
Query OK, 1 row affected (0.65 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yidiankt           |
+--------------------+
5 rows in set (0.26 sec)
mysql> 

[root@localhost etc]#  vi /etc/my.cnf
 
 #主服务器配置文件 my.cnf的配置（添加以下内容）
 log-bin=master-a-bin       #日志文件名称
 binlog-format=ROW          #二进制日志的格式有row，statement和mixed三种类型
 server-id=1                #要求各个服务器的这个id必须不一样
 binlog-do-db=yidiankt      #同步的数据库名称

#重新启动mysql服务
shell> systemctl restart mysqld

[root@localhost etc]#  vi /etc/my.cnf

#从服务器配置文件 my.cnf的配置（添加以下内容）
 log-bin=master-a-bin       #日志文件名称和主服务器一一致
 binlog-format=ROW          #二进制日志的格式，和主服务器一致
 server-id=2                #要求各个服务器的这个id必须不一样
#中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的服务器需要作为另一个服务器的主服务器的时候需要
#打开。就是双主互相备份，或者多主循环备份。
log-slave-updates=true

#重新启动mysql服务
shell>systemctl restart mysqld

#1.在主服务器中新建一个可以让从机登录的用户并且给该用户授权
CREATE USER 'yaotao'@'192.168.40.129' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'yaotao'@'192.168.40.129';
flush privileges;
#2.查看主机的状态这时可以看到log-bin文件的名还有position需要记录下来一会从机配置需要用
SHOW MASTER STATUS;
#  **记下position+file**

#在从服务器中执行下列命令
CHANGE MASTER TO
MASTER_HOST='192.168.40.128',      #主服务器的IP地址
MASTER_USER='yaotao',              #被授权的账号
MASTER_PASSWORD='123456',          #被授权的密码
MASTER_LOG_FILE='master-a-bin.000001', 
MASTER_LOG_POS=10265;

start slave;
show slave status\G;

#在主服务器中新建一个可以让从机登录的用户并且给该用户授权
CREATE USER '用户名'@'从机ip' IDENTIFIED WITH mysql_native_password BY '登陆密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从机ip';
flush privileges;
SHOW MASTER STATUS;

#在从服务器中执行下列命令
CHANGE MASTER TO
MASTER_HOST='主机ip',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='记下的file',
MASTER_LOG_POS=记下的position;
mysql> start slave;
mysql> stop slave;

mysql> CREATE USER 'yaotao'@'192.168.40.129' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'yaotao'@'192.168.40.129';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000001 |     1354 | yidiankt     |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.40.128',
    -> MASTER_USER='yaotao',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='master-a-bin.000001',
    -> MASTER_LOG_POS=1354;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.128
                  Master_User: yaotao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-a-bin.000001
          Read_Master_Log_Pos: 1354
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: master-a-bin.000001
                     此处为yes即正确
             Slave_IO_Running: Yes
                     此处为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: 1354
              Relay_Log_Space: 537
              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: 24b2a333-2a02-11ea-a94e-000c2937ea2f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 
1 row in set (0.00 sec)

create user 'username'@'host' identified by 'password';
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

grant all privileges on *.* to 'username'@'%';

flush privileges;

#mysql之前版本的授提示意思是不能用grant创建用户，mysql8.0以前的版本可以使用grant在授权的时候隐式的创建用户，8.0以后已经不支持，所以必须先创建用户，然后再授权，命令如下：权方式
grant replication slave on *.* to 'slave'@'%' identified by '123456';
#方式一
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT all privileges ON *.* TO 'slave'@'%';
#方式二
create user 'username'@'host' identified by 'password';
grant all privileges on *.* to 'username'@'%' ;


GRANT all privileges ON *.* TO 'root'@'%';

#这是一种可行的方法
CREATE USER 'yaotao'@'192.168.40.129' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'yaotao'@'192.168.40.129';
flush privileges;

CREATE USER 'user01'@'192.168.40.132' IDENTIFIED WITH mysql_native_password BY '123456';
grant all privileges on *.* to 'user01'@'192.168.40.132' with grant option;
flush privileges;

CHANGE MASTER TO
MASTER_HOST='192.168.40.128',
MASTER_USER='user01',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='master-a-bin.000001',
MASTER_LOG_POS=7609;

CREATE USER 'user'@'192.168.40.128' IDENTIFIED WITH mysql_native_password BY '123456';
grant all privileges on *.* to 'user'@'192.168.40.128' with grant option;
flush privileges;

CREATE TABLE `customer` (
  `CNO` VARCHAR(20) NOT NULL COMMENT '编号',
  `Name` VARCHAR(20) NOT NULL COMMENT '姓名',
  `Sex` VARCHAR(4) NOT NULL COMMENT '性别',
  `Principalship` VARCHAR(10) NULL COMMENT '职务',
  `Company` VARCHAR(40) NULL COMMENT '公司/单位',
  `Telephone` VARCHAR(20) NULL COMMENT '公司电话',
  `Address` VARCHAR(40) NULL COMMENT '公司地址',
  `Background` VARCHAR(80) NULL COMMENT '公司背景',
  PRIMARY KEY (`CNO`))	
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

INSERT INTO `customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2008002',
'郭华',
'女',
'董事长',
'光华集团',
'8221089',
'深圳',
'上市公司');

INSERT INTO `customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2007001',
'胡振',
'男',
'总经理',
'华夏大邦',
'8226858',
'上海',
'上市公司');
INSERT INTO `customer`
(`CNO`,
`Name`,
`Sex`,
`Principalship`,
`Company`,
`Telephone`,
`Address`,
`Background`)
VALUES
('2007002',
'李兴',
'男',
'经理',
'九州方圆',
'6182755',
'北京',
'上市公司');

 错误实例：
 show slave status\G 
 Slave_IO_Running: Yes 
 Slave_SQL_Running: No 
 
#在MySQL中执行
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
START SLAVE;  

mysql> use mysql ;
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

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

docker run -d -p 4300:3306 --name=mysql-300 -v /datavol/mysql-300/conf:/etc/mysql/conf.d -v /datavol/mysql-300/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql

docker run -d -p 4400:3306 --name=mysql-400 -v /datavol/mysql-400/conf:/etc/mysql/conf.d -v /datavol/mysql-400/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql

docker run -d -p 4500:3306 --name=mysql-500 -v /datavol/mysql-500/conf:/etc/mysql/conf.d -v /datavol/mysql-500/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql

docker run -d -p 4345:3306 --name=mysql-10345 -v /datavol/mysql-10345/conf:/etc/mysql/conf.d -v /datavol/mysql-10345/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql

#在Windows控制台登录远程数据库
mysql -uroot -p -h192.168.40.128 -P3306
#查询数据库引擎
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.15 sec)

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.11 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)


mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.04 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(60) | NO   |     | NULL    |                |
| age   | int(3)      | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql> create database westos;
Query OK, 1 row affected (0.03 sec)

exit  或者  \q   #退出mysql数据库

create database [if not exists] westos;

drop database [if exists] westos;

查看当前 mysqladmin的版本
[root@localhost ~]# mysqladmin -v
mysqladmin  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)
使用mysqladmin工具连接到MySQL并执行version命令查看MySQL数据库版本
[root@localhost ~]# mysqladmin -u root -p version
Enter password: 
mysqladmin  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2019, 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.

Server version		8.0.18
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
#表示MySQL服务器已经运行的描述
Uptime:			1 hour 15 min 10 sec
#活动线程的数量
Threads: 4  
#从MySQL启动以来客户问题的数量（查询）
Questions: 119  
#执行时间超过long_query_time秒的查询的数量
Slow queries: 0  
#服务器已打开的数据库的数量
Opens: 158  
#服务器已执行的flush...、refresh和reload命令的数量
Flush tables: 3  
#目前大开的表的数量
Open tables: 78  
#执行平均用时秒数
Queries per second avg: 0.026

[root@localhost ~]# mysqladmin -u root -p processlist
Enter password: 

#mysqlshow工具连接数据库，并显示MySQL上所有数据库
[root@localhost ~]# mysqlshow -u root -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| testdb             |
| yidiankt           |
+--------------------+
#mysqlshow工具连接数据库，并显示school数据库下的所有数据表名称
[root@localhost ~]# mysqlshow school -u root -p
Enter password: 
Database: school
+---------+
| Tables  |
+---------+
| student |
+---------+
# -v显示行数
[root@localhost ~]# mysqlshow school -v -u root -p
Enter password: 
Database: school
+---------+----------+
| Tables  | Columns  |
+---------+----------+
| student |        3 |
+---------+----------+
1 row in set.

#查看所有的存储引擎  \G可以让显示结果更加美观
mysql> show engines\G
*************************** 1. row ***************************
     #数据库存储引擎的名称
      Engine: FEDERATED
      #表示MySQL是否支持该类引擎，YES表示支持，NO表示不支持
     Support: NO
     #表示对该引擎的注释信息
     Comment: Federated MySQL storage engine
     #表示是否支持事务处理，YES表示支持，NO表示不支持
Transactions: NULL
#表示是否分布式交易处理的XA规范，YES表示支持，NO表示不支持
          XA: NULL
          #表示是否支持保存点，以便事务回滚到保存点，YES表示支持，NO表示不支持
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

#查看错误日志文件所在的目录及文件名信息
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)
#查看错误日志文件
[root@localhost ~]# vim /var/log/mysqld.log 
#删除错误日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.51 sec)
#也可在my.cnf文件中看
[root@localhost ~]# cat /etc/my.cnf


#查看通用查询日志，默认关闭
mysql> show variables like 'general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
#启用查询日志
mysql> set global general_log=on;
Query OK, 0 rows affected (0.03 sec)
mysql> set global general_log=0;
Query OK, 0 rows affected (0.02 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)


[root@localhost ~]# vim /var/lib/mysql/localhost.log 
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

#查看缓存
mysql> show variables like '%cache%';
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 32768                |
| binlog_stmt_cache_size          | 32768                |
| have_query_cache                | NO                   |
| host_cache_size                 | 279                  |
| innodb_disable_sort_file_cache  | OFF                  |
| innodb_ft_cache_size            | 8000000              |
| innodb_ft_result_cache_limit    | 2000000000           |
| innodb_ft_total_cache_size      | 640000000            |
| key_cache_age_threshold         | 300                  |
| key_cache_block_size            | 1024                 |
| key_cache_division_limit        | 100                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_stmt_cache_size      | 18446744073709547520 |
| schema_definition_cache         | 256                  |
| stored_program_cache            | 256                  |
| stored_program_definition_cache | 256                  |
| table_definition_cache          | 2000                 |
| table_open_cache                | 4000                 |
| table_open_cache_instances      | 16                   |
| tablespace_definition_cache     | 256                  |
| thread_cache_size               | 9                    |
+---------------------------------+----------------------+
21 rows in set (0.00 sec)

#查询mysql数据库配置文件的地址
[root@localhost ~]# whereis my.cnf
my: /etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid







 
 #主从复制，主配置文件 my.cnf的配置
 log-bin=master-a-bin       #日志文件名称
 binlog-format=ROW          #二进制日志的格式有row，statement和mixed三种类型
 server-id=1                #要求各个服务器的这个id必须不一样
 binlog-do-db=yidiankt      #同步的数据库名称（紧同步指定名称的数据库）
 
 
 #忽略指定名称的数据库
 #Binlog-ignore-db=db_name（数据库名称）
 
 
 #查看二进制日志的上限
 mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
#查看二进制日志相关信息（log_bin的值为ON，表名二进制日志打开成功）
mysql> show variables like 'log_bin%'\G
*************************** 1. row ***************************
Variable_name: log_bin
        Value: ON
*************************** 2. row ***************************
Variable_name: log_bin_basename
        Value: /var/lib/mysql/master-a-bin
*************************** 3. row ***************************
Variable_name: log_bin_index
        Value: /var/lib/mysql/master-a-bin.index
*************************** 4. row ***************************
Variable_name: log_bin_trust_function_creators
        Value: OFF
*************************** 5. row ***************************
Variable_name: log_bin_use_v1_row_events
        Value: OFF
5 rows in set (0.01 sec)

#查看二进制日志
 mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show binary logs;
+---------------------+-----------+-----------+
| Log_name            | File_size | Encrypted |
+---------------------+-----------+-----------+
| master-a-bin.000047 |       604 | No        |
| master-a-bin.000048 |       178 | No        |
| master-a-bin.000049 |       178 | No        |
| master-a-bin.000050 |       155 | No        |
| master-a-bin.000051 |       155 | No        |
| master-a-bin.000052 |       178 | No        |
| master-a-bin.000053 |       155 | No        |
| master-a-bin.000054 |       178 | No        |
| master-a-bin.000055 |       155 | No        |
| master-a-bin.000056 |       155 | No        |
| master-a-bin.000057 |       178 | No        |
| master-a-bin.000058 |       178 | No        |
| master-a-bin.000059 |       178 | No        |
| master-a-bin.000060 |       178 | No        |
| master-a-bin.000061 |       178 | No        |
| master-a-bin.000062 |       178 | No        |
| master-a-bin.000063 |       178 | No        |
| master-a-bin.000064 |       178 | No        |
| master-a-bin.000065 |       178 | No        |
| master-a-bin.000066 |       178 | No        |
| master-a-bin.000067 |       178 | No        |
| master-a-bin.000068 |       178 | No        |
| master-a-bin.000069 |       178 | No        |
| master-a-bin.000070 |       178 | No        |
| master-a-bin.000071 |       155 | No        |
| master-a-bin.000072 |       178 | No        |
| master-a-bin.000073 |       205 | No        |
| master-a-bin.000074 |       205 | No        |
| master-a-bin.000075 |       205 | No        |
| master-a-bin.000076 |       205 | No        |
| master-a-bin.000077 |       205 | No        |
| master-a-bin.000078 |       205 | No        |
| master-a-bin.000079 |       205 | No        |
| master-a-bin.000080 |       205 | No        |
| master-a-bin.000081 |       205 | No        |
| master-a-bin.000082 |       155 | No        |
| master-a-bin.000083 |       155 | No        |
+---------------------+-----------+-----------+
37 rows in set (0.27 sec)

#查看指定的二进制文件的内容（对数据库操作的记录），作为对数据库进行管理和数据恢复的依据
mysql> show binlog events in 'master-a-bin.000083'\G
*************************** 1. row ***************************
   Log_name: master-a-bin.000083
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 124
       Info: Server ver: 8.0.18, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: master-a-bin.000083
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 155
       Info: 
2 rows in set (0.00 sec)

#删除指定编号前的所有日志
mysql> purge master logs to 'master-a-bin.000066';
Query OK, 0 rows affected (0.01 sec)
#查看二进制文件
mysql> show binary logs;
+---------------------+-----------+-----------+
| Log_name            | File_size | Encrypted |
+---------------------+-----------+-----------+
| master-a-bin.000066 |       178 | No        |
| master-a-bin.000067 |       178 | No        |
| master-a-bin.000068 |       178 | No        |
| master-a-bin.000069 |       178 | No        |
| master-a-bin.000070 |       178 | No        |
| master-a-bin.000071 |       155 | No        |
| master-a-bin.000072 |       178 | No        |
| master-a-bin.000073 |       205 | No        |
| master-a-bin.000074 |       205 | No        |
| master-a-bin.000075 |       205 | No        |
| master-a-bin.000076 |       205 | No        |
| master-a-bin.000077 |       205 | No        |
| master-a-bin.000078 |       205 | No        |
| master-a-bin.000079 |       205 | No        |
| master-a-bin.000080 |       205 | No        |
| master-a-bin.000081 |       205 | No        |
| master-a-bin.000082 |       155 | No        |
| master-a-bin.000083 |       155 | No        |
+---------------------+-----------+-----------+
18 rows in set (0.00 sec)

#删除指定日期前的日志
mysql> purge master logs before '20180808';
Query OK, 0 rows affected (0.00 sec)

#删除所有日志
mysql> reset master;

#查询数据库的存储地址
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)
#也可在my.cnf文件中看
[root@localhost ~]# cat /etc/my.cnf





#查看二进制日志是否开启（log_bin的值为ON，说明二进制日志已成功开启）
mysql> show variables like '%log_bin%'\G
*************************** 1. row ***************************
Variable_name: log_bin
        Value: ON
*************************** 2. row ***************************
Variable_name: log_bin_basename
        Value: /var/lib/mysql/master-a-bin
*************************** 3. row ***************************
Variable_name: log_bin_index
        Value: /var/lib/mysql/master-a-bin.index
*************************** 4. row ***************************
Variable_name: log_bin_trust_function_creators
        Value: OFF
*************************** 5. row ***************************
Variable_name: log_bin_use_v1_row_events
        Value: OFF
*************************** 6. row ***************************
Variable_name: sql_log_bin
        Value: ON
6 rows in set (0.00 sec)

#当前用户的连接信息的操作
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: system user
   Host: connecting host
     db: NULL
Command: Connect
   Time: 33450
  State: Connecting to master
   Info: NULL
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host: 
     db: NULL
Command: Query
   Time: 33450
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 3. row ***************************
     Id: 6
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 33450
  State: Waiting on empty queue
   Info: NULL
*************************** 4. row ***************************
     Id: 458
   User: root
   Host: localhost
     db: school
Command: Sleep
   Time: 5869
  State: 
   Info: NULL
*************************** 5. row ***************************
     Id: 529
   User: root
   Host: 192.168.40.1:58178
     db: school
Command: Sleep
   Time: 3128
  State: 
   Info: NULL
*************************** 6. row ***************************
     Id: 530
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 7. row ***************************
     Id: 580
   User: root
   Host: 192.168.40.1:59815
     db: school
Command: Sleep
   Time: 199
  State: 
   Info: NULL
7 rows in set (0.00 sec)

#charset返回指定参数使用的字符集
mysql> select charset('test'),charset(convert('test' USING latin1)),charset(version())\G
*************************** 1. row ***************************
                      charset('test'): utf8mb4
charset(convert('test' USING latin1)): latin1
                   charset(version()): utf8
1 row in set (0.00 sec)

show databases ;
use example;
create table student(
    ID int auto_increment not null primary key ,
    NAME varchar(40)
);
insert into student values (NULL,'张三');
insert into student values (NULL,'李四');
select * from student;
#查询最后插入的一条记录的id
select LAST_INSERT_ID();
insert into student values (NULL,'王五'),(null,'赵六'),(null,'周三');
select last_insert_id();

#使用MD5（str）函数返回加密字符串的操作
mysql> select md5('test');
+----------------------------------+
| md5('test')                      |
+----------------------------------+
| 098f6bcd4621d373cade4e832627b4f6 |
+----------------------------------+
1 row in set (0.00 sec)

C:\Users\Administrator>mysql -uroot -p123456
#创建一个新用户
mysql> create user hyc@localhost identified by '123456';
#给用户授权（只是把mysql数据库中的user表的增删改查授权给新添加的用户）
mysql> grant select,update,delete,insert on mysql.user to hyc@localhost;
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
mysql> show grants for hyc@localhost;
+-----------------------------------------------------------------------------+
| Grants for hyc@localhost                                                    |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hyc`@`localhost`                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO `hyc`@`localhost` |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#与之对应的取消授权
mysql> revoke select,update,delete,insert on mysql.user from hyc@localhost;
Query OK, 0 rows affected (1.74 sec)


#
C:\Users\Administrator>mysql -uhyc -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)
mysql> use mysql ;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user            |
+-----------------+
1 row in set (0.06 sec)

[root@localhost ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Market             |
| aa                 |
| example            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
| test_db            |
| yidiankt           |
+--------------------+
11 rows in set (0.01 sec)

mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | kuangshen |   3 |
+----+-----------+-----+
1 row in set (0.00 sec)

mysql> 
#备份数据库
[root@localhost ~]# mysqldump -uroot -p123456  school > /newdisk/schoolDB.dump
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#删除数据库
mysql> drop database school;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Market             |
| aa                 |
| example            |
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| test_db            |
| yidiankt           |
+--------------------+
10 rows in set (0.00 sec)
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school;
Database changed
mysql> show tables;
Empty set (0.00 sec)

#还原数据库
[root@localhost ~]# mysql -uroot -p123456  school < /newdisk/schoolDB.dump
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec)
mysql> select * from student;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | kuangshen |   3 |
+----+-----------+-----+
1 row in set (0.00 sec)

[root@localhost ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
#备份所有的数据库
[root@localhost newdisk]# mysqldump -uroot -p123456  --all-databases >/newdisk/alldb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#备份某几个数据库
[root@localhost newdisk]# mysqldump -uroot -p123456  --databases school  yidiankt girls   >/newdisk/sch_yi_gir.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#备份一个数据库
[root@localhost newdisk]# mysqldump -uroot -p123456 student >/newdisk/studb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#备份指定数据库的一个表
[root@localhost newdisk]# mysqldump -uroot -p123456 student major >/newdisk/student_major.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cd /newdisk/
[root@localhost newdisk]# ll
总用量 18316
-rw-r--r--. 1 root root 16635040 12月 13 10:04 1421418.PDF
-rw-r--r--. 1 root root  1052967 4月  25 08:02 alldb.sql
-rw-r--r--. 1 root root  1034515 4月  25 08:13 create
-rw-r--r--. 1 root root     2938 4月  25 08:07 girls.sql
-rw-r--r--. 1 root root     6713 4月  25 08:14 sch_yi_gir.sql
-rw-r--r--. 1 root root     4911 4月  25 08:15 studb.sql
-rw-r--r--. 1 root root     1951 4月  25 08:16 student_major.sql
#查看备份的数据库
[root@localhost newdisk]# more sch_yi_gir.sql 
-- MySQL dump 10.13  Distrib 8.0.18, for Linux (x86_64)
--
-- Host: localhost    Database: school
-- ------------------------------------------------------
-- Server version	8.0.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `school`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*
!80016 DEFAULT ENCRYPTION='N' */;

USE `school`;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '学院id',
  `name` varchar(60) NOT NULL COMMENT '学员姓名',
  `age` int(3) NOT NULL COMMENT '学员年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'kuangshen',3);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Current Database: `yidiankt`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `yidiankt` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
/*!80016 DEFAULT ENCRYPTION='N' */;

USE `yidiankt`;

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `customer` (
  `CNO` varchar(20) NOT NULL COMMENT '编号',
  `Name` varchar(20) NOT NULL COMMENT '姓名',
  `Sex` varchar(4) NOT NULL COMMENT '性别',
  `Principalship` varchar(10) DEFAULT NULL COMMENT '职务',
  `Company` varchar(40) DEFAULT NULL COMMENT '公司/单位',
  `Telephone` varchar(20) DEFAULT NULL COMMENT '公司电话',
  `Address` varchar(40) DEFAULT NULL COMMENT '公司地址',
  `Background` varchar(80) DEFAULT NULL COMMENT '公司背景',
  PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `customer`
--

LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES ('2008002','郭华','女','董事长','光华集团','8221089','深圳','上市公司');
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Current Database: `girls`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `girls` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!
80016 DEFAULT ENCRYPTION='N' */;

USE `girls`;

--
-- Table structure for table `admin`
--

DROP TABLE IF EXISTS `admin`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `admin`
--

LOCK TABLES `admin` WRITE;
/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
INSERT INTO `admin` VALUES (1,'john','8888'),(2,'lyt','6666');
/*!40000 ALTER TABLE `admin` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `beauty`
--

DROP TABLE IF EXISTS `beauty`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `beauty`
--

LOCK TABLES `beauty` WRITE;
/*!40000 ALTER TABLE `beauty` DISABLE KEYS */;
INSERT INTO `beauty` VALUES (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00
:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00
:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 0
0:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00
:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00
:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:
00:00','18209179577',NULL,1);
/*!40000 ALTER TABLE `beauty` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `boys`
--

DROP TABLE IF EXISTS `boys`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `boys`
--

LOCK TABLES `boys` WRITE;
/*!40000 ALTER TABLE `boys` DISABLE KEYS */;
INSERT INTO `boys` VALUES (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40000 ALTER TABLE `boys` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-04-25  8:14:12



#还原数据库
mysql> create database girls;
Query OK, 1 row affected (0.02 sec)
[root@localhost ~]# mysql -uroot -p123456 girls < /newdisk/girls.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.