MySQL和Linux试题_Linux运维必会的MySql题之（一）

如何启动MySql服务

/etc/init.d/mysqld start

service mysqld start

Centos7.x 系统

systemctl start mysqld

检测端口是否运行

[root@localhost ~]# lsof -i :3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld17026 mysql 18u IPv6 49416 0t0 TCP *:mysql (LISTEN)

[root@localhost ~]# netstat -lntup |grep :3306

tcp6 0 0 :::3306 :::* LISTEN 17026/mysqld

设置或修改MySql密码

mysql5.6之前修改密码(字段password)

mysql>use mysql;

mysql> update user set password=password('123') where user='root' and host='localhost';

mysql>flush privileges;

mysql5.7版本修改密码(字段是authentication_string)mysql>alter user 'root'@'localhost' identified by '123';mysql>flush privileges;

或者

mysql> use mysql;

mysql> update mysql.user set authentication_string = password('123456') where user = 'root' and host = 'localhost';

Query OK, 1 row affected, 1 warning (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.05 sec)

使用mysqladmin工具 格式：mysqladmin-u用户名 -p旧密码 password 新密

[root@localhost ~]# mysqladmin -uroot -p123 password 'root123'

Warning: Using a password on the command line interface can be insecure.

[root@localhost ~]# mysql -uroot -proot123

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.6.47 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

登陆数据库

[root@localhost ~]# mysql -uroot -proot123

查看当前数据库的字符集

mysql>show create database mysql;+----------+------------------------------------------------------------------+

| Database | Create Database |

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

| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1*/ |

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

1 row in set (0.00 sec)

查看当前数据库版本

[root@localhost ~]# mysql -uroot -p123 -e "use mysql;select version();"mysql: [Warning] Using a password on the command line interface can be insecure.+-----------+

| version() |

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

| 5.7.29 |

+-----------+[root@localhost~]# mysql -V

mysql Ver14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

查看当前登录用户

[root@localhost ~]# mysql -uroot -p123 -e "select user();"mysql: [Warning] Using a password on the command line interface can be insecure.+----------------+

| user() |

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

| root@localhost |

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

select user();  #进入数据库查询

创建GBK字符集数据库mingongge并查看完整创建语句

mysql>create database mingongge default charset gbk collate gbk_chinese_ci;

Query OK,1 row affected (0.00 sec)

创建用户mingongge使用之可以管理数据库mingongge

mysql> grant all on mingongge.* to 'mingongge'@'localhost' identified by 'mingongge'

->;

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

查看创建用户mingongge的权限

mysql> show grants formingongge@localhost;+------------------------------------------------------------------+

| Grants for mingongge@localhost |

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

| GRANT USAGE ON *.* TO 'mingongge'@'localhost' |

| GRANT ALL PRIVILEGES ON `mingongge`.* TO 'mingongge'@'localhost' |

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

2 rows in set (0.00 sec)

查看当前数据库有哪此用户

mysql> selectuser from mysql.user;+---------------+

| user |

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

| mingongge |

| mysql.session |

| mysql.sys |

| root |

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

4 rows in set (0.01 sec)

进入mingongge数据库

mysql>use mingongge

Database changed

创建一个innodb GBK表test,字段id int(4)和name varchar(16)

mysql>create table test (-> d int(4),-> name varchar(16)-> )ENGINE=innodb DEFAULT CHARSET=gbk;

Query OK,0 rows affected (0.30 sec)

查看建表结构及表结构的SQL语句

mysql>desc test;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| d | int(4) | YES | | NULL | |

| name | varchar(16) | YES | | NULL | |

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

2 rows in set (0.05sec)

mysql>show create table test\G*************************** 1. row ***************************Table: test

Create Table: CREATE TABLE `test` (

`d`int(4) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.00 sec)

插入一条数据“1,mingongge”

mysql> insert into test values('1','mingongge');

Query OK,1 row affected (0.01 sec)

再批量插入2行数据“2,ljj”,“3,mingonggeedu”

mysql> insert into test values('2','ljj'),('3','mingonggeedu');

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0

查询名字为mingongge的记录

mysql> select * from test where name = 'mingongge';+------+-----------+

| d | name |

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

| 1 | mingongge |

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

1 row in set (0.05 sec)

把数据id等于1的名字mingongge更改为mgg

mysql> update test set name = 'mgg' where d = '1';

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0

在字段name前插入age字段，类型tinyint(2)

mysql> alter table test add age tinyint(2) after d;

Query OK,0 rows affected (0.17sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc test->;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| d | int(4) | YES | | NULL | |

| age | tinyint(2) | YES | | NULL | |

| name | varchar(16) | YES | | NULL | |

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

3 rows in set (0.00 sec)

不退出数据库,完成备份mingongge数据库

mysql> system mysqldump -uroot -p123 -B mingongge >/root/mingongge_bak.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost~]# ls mingongge_bak.sql -l-rw-r--r-- 1 root root 2044 1月 15 10:57 mingongge_bak.sql