mysql 常用命令

linux如何查看所有的用户和组信息
https://www.cnblogs.com/xiohao/p/5877256.html

https://www.mysql.com/downloads/ 下载
https://www.cnblogs.com/xinjing-jingxin/p/8025805.html

https://dev.mysql.com/downloads/file/?id=476936
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

https://www.cnblogs.com/zhuiluoyu/p/5453579.html 安装
https://www.cnblogs.com/hjw-zq/archive/2018/04/12/8809227.html

　groupadd  mysql //创建mysql 用户组

　　useradd -g mysql mysql //创建一个用户名为mysql的用户并加入mysql用户组


[client]

　　port = 3306
　　socket = /tmp/mysql.sock

　　[mysqld]
　　character_set_server=utf8
　　init_connect='SET NAMES utf8'
　　basedir=/usr/local/mysql
　　datadir=/usr/local/mysql/data
　　socket=/tmp/mysql.sock


character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'




[root@iZ28o38zswdZ support-files]# cat my.cnf
[client]
default-character-set = utf8mb4

[mysql]
#开启 tab 补全
#auto-rehash
default-character-set = utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应:
collation-server = utf8mb4_general_ci
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'

[mysqld]
port=3306
basedir=/usr/local/soft/mysql/
datadir=/usr/local/soft/mysql/data/
socket=/tmp/mysql.sock
symbolic-links=0
log-error=/usr/local/soft/mysql/logs/mysqld.log
pid-file=/usr/local/soft/mysql/data/mysqld.pid
[root@iZ28o38zswdZ support-files]#

初始化
/usr/local/soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/soft/mysql/ --datadir=/usr/local/soft/mysql/data/


C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.35 MySQL Community Server (GPL)

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


5.
mysql> update user set password = PASSWORD('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

================================linux==================


#启动mysql

service mysqld start

#关闭mysql

service mysqld stop

#查看运行状态

service mysqld status

#加入环境变量，编辑 /etc/profile，这样可以在任何地方用mysql命令了

[root@localhost mysql]# vi /etc/profile
环境变量配置 配置后使用source /ete/profiel使其生效
export PATH=$PATH:/usr/local/soft/mysql

export JAVA_HOME=/usr/local/java/jdk1.8.0_101
export MAVEN_HOME=/usr/local/soft/maven
export CLASSPATH=$JAVA_HOME/jre/lib:$JAVA_HOME/lib
export PATH=$PATH:$JAVA_HOME/bin:$MAVEN_HOME/bin
export PATH=$PATH:/usr/local/soft/mysql/bin

配置别名
alias mysql3306='mysql -uroot -p123456'

我们只需要编辑/root/.bashrc在里面添加上我们需要的别名设置保存退出即可。
vim ./.bashrc
添加上面alias

source /root/.bashrc



mysql
http://mirrors.sohu.com/mysql/
http://jingyan.baidu.com/article/67508eb439d2849ccb1ce45b.html

wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz

wget http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-server-5.6.31-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-client-5.6.31-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-devel-5.6.31-1.el7.x86_64.rpm
MySQL-devel-5.6.31-1.el7.x86_64.rpm

mysql 安装
http://blog.csdn.net/wb96a1007/article/details/51559741


1、下载安装包
http://dev.mysql.com/downloads/mysql/#downloads
推荐下载通用安装方法的TAR包（http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.12-linux-glibc2.5-x86_64.tar）。

2.检查库文件是否存在，如有删除。
[root@localhost Desktop]$
mysql-libs-5.1.52-1.el6_0.1.x86_64
[root@localhost ~]# rpm -e mysql-libs-5.1.52.x86_64 --nodeps
[root@localhost ~]#

3.检查mysql组和用户是否存在，如无创建。
[root@localhost ~]# cat /etc/group | grep mysql
mysql:x:490:
[root@localhost ~]# cat /etc/passwd | grep mysql
mysql:x:496:490::/home/mysql:/bin/bash
以上为默认存在的情况，如无，执行添加命令：
[root@localhost ~]#groupadd mysql
[root@localhost ~]#useradd -r -g mysql mysql
//useradd -r参数表示mysql用户是系统用户，不可用于登录系统。

4.解压TAR包，更改所属的组和用户
[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar xvf mysql-5.7.12-linux-glibc2.5-x86_64.tar
[root@localhost local]# ls -l
total 1306432
-rwxr--r--. 1 root root  668866560 Jun  1 15:07 mysql-5.7.12-linux-glibc2.5-x86_64.tar
-rw-r--r--. 1 7161 wheel 638960236 Mar 28 12:54 mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 7161 wheel  29903372 Mar 28 12:48 mysql-test-5.7.12-linux-glibc2.5-x86_64.tar.gz
[root@localhost local]# tar xvfz mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
[root@localhost local]# mv mysql-5.7.12-linux-glibc2.5-x86_64 mysql
[root@localhost local]# ls -l
total 1306436
drwxr-xr-x. 2 root root       4096 Dec  4  2009 bin
drwxr-xr-x. 2 root root       4096 Dec  4  2009 etc
drwxr-xr-x. 2 root root       4096 Dec  4  2009 games
drwxr-xr-x. 2 root root       4096 Dec  4  2009 include
drwxr-xr-x. 2 root root       4096 Dec  4  2009 lib
drwxr-xr-x. 3 root root       4096 Dec  2 14:36 lib64
drwxr-xr-x. 2 root root       4096 Dec  4  2009 libexec
drwxr-xr-x. 9 7161 wheel      4096 Mar 28 12:51 mysql
-rwxr--r--. 1 root root  668866560 Jun  1 15:07 mysql-5.7.12-linux-glibc2.5-x86_64.tar
-rw-r--r--. 1 7161 wheel 638960236 Mar 28 12:54 mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 7161 wheel  29903372 Mar 28 12:48 mysql-test-5.7.12-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x. 2 root root       4096 Dec  4  2009 sbin
drwxr-xr-x. 6 root root       4096 Dec  2 14:36 share
drwxr-xr-x. 2 root root       4096 Dec  4  2009 src
[root@localhost local]# chown -R mysql mysql/
[root@localhost local]# chgrp -R mysql mysql/
[root@localhost local]# cd mysql/

rpm -ivh MySQL-client-5.6.15-1.el6.x86_64.rpm

http://www.cnblogs.com/nuoyinsomnus/p/5702681.html 安装


5.安装和初始化数据库
[root@localhost mysql]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2016-06-01 15:23:25 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2016-06-01 15:23:30 [WARNING] The bootstrap log isn't empty:
2016-06-01 15:23:30 [WARNING] 2016-06-01T22:23:25.491840Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2016-06-01T22:23:25.492256Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-06-01T22:23:25.492260Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
----------------------
如果改变默认安装路径，则需要
1）/etc/my.cnf、/etc/init.d/mysqld中修改
basedir='/apps/mysql'
datadir='/apps/mysql/data'
2）创建ln
mkdir -p /usr/local/mysql/bin
ln -s /apps/mysql/bin/mysqld /usr/local/mysql/bin/mysqld
----------------------

[root@localhost mysql]#

[root@localhost mysql]# cp -a ./support-files/my-default.cnf /etc/my.cnf
[root@localhost mysql]# cp -a ./support-files/mysql.server  /etc/init.d/mysqld

[root@localhost mysql]# cd bin/
[root@localhost bin]# ./mysqld_safe --user=mysql &
[1] 2932
[root@localhost bin]# 2016-06-01T22:27:09.708557Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
2016-06-01T22:27:09.854913Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@localhost bin]# /etc/init.d/mysqld restart
Shutting down MySQL..2016-06-01T22:27:50.498694Z mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
SUCCESS!
Starting MySQL. SUCCESS!
[1]+  Done                    ./mysqld_safe --user=mysql
[root@localhost bin]#
//设置开机启动
[root@localhost bin]# chkconfig --level 35 mysqld on
[root@localhost bin]#

6.初始化密码
mysql5.7会生成一个初始化密码，而在之前的版本首次登陆不需要登录。
[root@localhost bin]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2016-06-01 15:23:25   jlBwwCf?C3s6  skip-grant-tables
,xxxxxR5H9
[root@localhost bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12

Copyright (c) 2000, 2016, 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> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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


7.添加远程访问权限

mysql> use mysql;
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> 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.sys |
+-----------+-----------+
重启生效
/etc/init.d/mysqld restart




Linux启动/停止/重启Mysql数据库的方法

1、查看mysql版本
方法一：status;
方法二：select version();

2、Mysql启动、停止、重启常用命令
a、启动方式
1、使用 service 启动：
[root@localhost /]# service mysqld start (5.0版本是mysqld)
[root@szxdb etc]# service mysql start (5.5.7版本是mysql)

2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld start

3、使用 safe_mysqld 启动：
safe_mysqld&

b、停止
1、使用 service 启动：
service mysqld stop

2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld stop

3、mysqladmin shutdown

c、重启
1、使用 service 启动：
service mysqld restart
service mysql restart (5.5.7版本命令)

2、使用 mysqld 脚本启动：
/etc/init.d/mysqld restart

#skip-grant-tables 忘记密码加上这个
grant all on *.* to root@'%' identified by 'peng1234567';
看不见表
在skip-grant-tables下将字段从N改成Y

http://blog.csdn.net/u010682362/article/details/42125317


修改字符集命令
        如果已经是创建好的对象，那又应该如何处理呢。我们就应该对指定对象就行修改字符集的操作。
1.修改character_set_connection、character_set_client、character_set_results三值：
对于某一个连接来说，可以使用：
SET NAMES 'charset_name' [COLLATE 'collation_name']


解决乱码的方法是，在执行SQL语句之前，将MySQL以下三个系统参数设置为与服务器字符集character-set-server相同的字符集。
character_set_client：客户端的字符集。
character_set_results：结果字符集。
character_set_connection：连接字符集。
设置这三个系统参数通过向MySQL发送语句：set names gb2312 /utf8mb4

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

http://blog.csdn.net/dqchouyang/article/details/51473544


254 mysql停止
[root@iZ28o38zswdZ ~]# /etc/init.d/mysqld stop
Shutting down MySQL... SUCCESS!






[root@iZ258z9dflbZ ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


导入大sql文件
1.
mysql> source /usr/local/data/job.sql;
2. mysql bin目录下执行
[root@iZ28o38zswdZ bin]# mysql -uroot -ppeng1234567 wechat < /usr/local/data/district.sql;

mysql -uroot_master -pp123456 wechat < /usr/local/data/wechat-export-1016.sql

导出：
[root@iZ28o38zswdZ bin]# mysqldump -uroot -ppeng1234567 wechat user > /usr/local/data/user_export.sql;
表结构
mysqldump -uroot -ppeng1234567 -d wechat user > /usr/local/data/user_desc.sql;