linux环境下部署MySQL及MySQL基础命令

一、环境准备

1.部署包列表

mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz

2.服务部署规划

使用软件包进行部署并使用

3.目录部署规划

• 安装目录为/usr/local/mysql
• 数据目录为/data/mysql

4.系统初始化

关闭防火墙和SELinux

IP地址192.168.200.30

二、安装部署

1.准备安装包

2.解压包至规划路径

 tar -zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
 cd /usr/local/
 mv mysql-5.6.43-linux-glibc2.12-x86_64/ mysql

 

3.创建用户和用户组并赋权

# useradd -s /sbin/nologin mysql
# groupadd mysql
# mkdir -p /data/mysql
# chown -R mysql:mysql /data/mysql

4.定义用户和数据目录

[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
-bash: ./scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录

[root@localhost mysql]#yum install -y perl-Module-Install

                //这里是应对上方的报错

[root@localhost mysql]#  ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

5.修改配置

# cd /usr/local/mysql/

#cp support-files/my-default.cnf /etc/my.cnf

#vi /etc/my.cnf //修改配置内容

[mysqld]

basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 30
socket = /tmp/mysql.sock
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/var/lib/mysql/slow.log
long_query_time=5 #查询超过5秒钟的语句记录下来
log-queries-not-using-indexes =1 #没有使用索引的查询

 

6.拷贝启动脚本

cp support-files/mysql.server  /etc//init.d/mysqld //拷贝启动脚本

7.修改脚本

cp support-files/mysql.server  /etc//init.d/mysqld //拷贝启动脚本

vi /etc/init.d/mysqld //修改启动脚本

basedir=/usr/local/mysql

datadir=/data/mysql

8.添加开机自启

chkconfig   --add mysqld //将mysql加到服务项
chkconfig mysqld on //设定开机自启

9.启动服务

service mysqld start

三、验证服务

1.进程

# ps -ef | grep mysqld

2.端口

# netstat -lnpt | grep 3306

3.日志

4.测试用例（服务基本操作: 增删改查）

增加环境变量

# export PATH=/usr/local/mysql/bin:$PATH

# /usr/local/mysql/bin/mysql -u root -p
Enter password:

 

1.设置密码

# mysqladmin -uroot password '123456'

 

2.查询库

mysql> show databases;

3.切换库

mysql> use mysql;

4.查询表

mysql> show tables;

 

5.查看字段

mysql> desc slow_log;

6.查看建表语句

mysql> show create table slow_log\G;

7.查看当前用户

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

8.查看当前数据库

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

9.创建数据库

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

10.创建表

mysql> create table t1(`id` int(4), `name` char(40));

11.查看数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.43 |
+-----------+
1 row in set (0.09 sec)

12.查看数据库状态

mysql> show status;

13.查看各项参数

mysql> show variables;

mysql> show variables like 'max_connect%';

14.修改参数

mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)

15.查看队列

mysql> show processlist;

16.查看表内行数

mysql> select count(*) from db;

17.查看表

mysql> select * from db;

18.查看表内字段

mysql> select db from db;

mysql> select db,user from db;

19.查看表中db为test的内容

mysql> select * from db where db like'test' ;

20.向表中插入数据

mysql> insert into t1 values(1, '150');

21.更改表的内容

mysql> update t1 set name='test' where id=1;

22.清空表的内容

mysql> truncate table t1;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from t1;
Empty set (0.00 sec)

23.删除表

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

24.删除数据库

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

25.备份数据库

[root@localhost ~]# mysqldump  -uroot -p123456 mysql > /tmp/mysql.sql

26.恢复数据库

[root@localhost ~]#mysql -uroot -p123456 mysql < /tmp/mysql.sql

27.备份表

[root@localhost ~]#mysql -uroot -p123456 mysql < /tmp/mysql.sql

28. 恢复表

mysql -uroot -plinux mysql < /tmp/user.sql