Mysql运维基础操作

Windows安装步骤

1、添加环境变量

C:\mysql-5.6.39-winx64\bin

2、设置my.ini

basedir=C:\mysql-5.6.39-winx64
datadir=C:\mysql-5.6.39-winx64\data
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3、安装mysql

管理员身份进cmd
$ cd C:\mysql-5.6.39-winx64\bin
$ mysqld -install
（如果安装失败，先运行安装vcredist_x64）
$ net start mysql
$ mysql -uroot -p

Linux跳过安装，下面是配置步骤

1、配置文件

编译配置文件my.cnf，在windows中为my.ini
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2、登录数据库

$ mysql -uroot -p -S /tmp/mysql.sock
注：-S参数指定sock登录

3、设置root密码

方式一：
> set password for root@localhost = password('root'); 
方式二：
> use mysql
> update user set password=password("root") where user='root';
> flush privileges;

4、删除数据库

> drop database `db`;

5、创建数据库设置编码

> CREATE DATABASE `db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

6、导入数据库

> use `db`;
> source /tmp/db.sql;
注：如果发生报错有两个可能。一个是外键限制，暂时先关闭检查；另外是因为配置文件的编码原因（如下修改）。

[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8

7、创建数据库账号及授权

1）如果语句一执行报错，就尝试语句二创建账号。
语句一：
> CREATE USER 'user'@'localhost' IDENTIFIED BY 'user_pwd';
语句二：
> insert into mysql.user(Host,User,Password) values("localhost","user",password("user_pwd"));
2）授权：
示例一：
> GRANT ALL ON `db`.* TO 'user'@'localhost';
示例二：
> GRANT PROCESS, FILE, SUPER, REPLICATION CLIENT ON *.* TO 'nagios'@'192.168.1.1' IDENTIFIED BY 'nagios';
给远程用户授权时注意设置密码，否则连不上哦。
3）刷新生效：
> flush privileges;
4）删除用户：
> delete from user where user='user' and host='localhost';

8、创建php文件测试

<?php
header("Content-Type: text/html; charset=utf-8") ;

//set_time_limit(0);
//error_reporting( E_ALL&~E_NOTICE );
 
$con = mysql_connect("localhost:3306","user","user_pwd");
if (!$con) {  die('Could not connect: ' . mysql_error());  }

mysql_select_db("db", $con);

$result = mysql_query("SELECT * FROM table");

while($row = mysql_fetch_array($result))
  {
  echo $row['user_id'] . " " . $row['user_pwd'];
  echo "<br />";
  }

mysql_close($con);

9、数据库改名

常见的主要有两种方法：
1）如果所有表都是MyISAM类型的话，可以直接修改文件夹的名字。
$ service mysqld stop
$ mv db_name new_db_name

2）利用mysqldump命令从旧的数据导出数据，再导入新数据库。
$ mysqldump -uroot -p db_name > db_name_dump.SQL
$ mysql -uroot -p -e `CREATE DATABASE new_db_name`
$ mysql -uroot -p new_db_name < db_name_dump.SQL
$ mysql -uroot -p -e `DROP DATABASE db_name`

10、常用语句

1）查看建库语句
> SHOW CREATE DATABASE `db`;

2）查看表结构
> desc `table`;

3）查看表类型（存储引擎）
> SHOW TABLE STATUS FROM `table`;

4）查看数据库默认编码
> show variables like '%char%';

5）查看数据库所有用户
> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

6）查看某个用户的权限
> show grants for 'root'@'localhost';   

7）条件导出数据表
> mysqldump -uroot -proot `db` `table` --where="id>0 and id<100" > /tmp/table.sql

8）获取最新的十条数据
> SELECT * FROM `table` ORDER BY date DESC LIMIT 10;

9）格式化输出查询数据
> select * from mysql.user\G;

10）查询数据表大小
> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables where table_schema=`db` AND table_name=`table`; 

11）查看数据库默认的存储引擎
> show variables like '%storage_engine%';

12）查看数据库的所有数据表名
> select table_name from information_schema.tables where table_schema='数据库';

11、导出数据库

1. 全量导出数据库
    $ mysqldump --lock-all-tables --flush-logs --master-data=2 -uroot -proot `db` > db.sql

2. 增量导出数据库
    1）执行增量备份的前提条件是MySQL打开binlog日志功能，在配置文件中加入
    [mysqld]
    log-bin=/db/mysql/data/log-bin/mysql-bin
    “log-bin=”后的字符串为日志记载目录，一般建议放在不同于MySQL数据目录的磁盘上。
    2）使用flush-logs命令来创建新日志
    > flush-logs
    
3.根据编码导出数据库
	$ mysqldump -uroot -proot --default-character-set=utf8 `db` > db.sql
	
4.导出导入数据表
	导出：
	$ mysqldump -uroot -proot `db` `table` > /tmp/table.sql
	导入：
	$ mysql -uroot -proot `db` < /tmp/table.sql

5.导出到cvs文件
	$ mysql -uroot -proot -e "select * from table" -ss | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/table.csv

12、忘记数据库密码

1.暂停数据库
$ /etc/init.d/mysqld stop

2.执行以下命令
$ /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=root &

3.使用root进入数据库，无需密码
$ mysql -uroot -p

13、不同版本mysql的初始化命令

1.mysql5.7版本初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql

2.mysql5.6版本初始化
/usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/db/mysql/data

参考链接：
1、The vervice already exists!-mysql安装服务
https://blog.csdn.net/snihcel/article/details/9464189