Linux运维必会的MySql题之（一）
如何启动MySql服务
/etc/init.d/mysqld start service mysqld start Centos 7.x 系统 systemctl start mysqld
检测端口是否运行
[root@localhost ~]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 17026 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; mysql 5.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 Ver 14.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 for mingongge@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> select user 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.05 sec) 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=gbk 1 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.00 sec) 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.00 sec) 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.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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
