MySql管理合集

安装

1、CentOS

sudo yum install -y mysql-server mysql mysql-devel

用Docker快捷部署MySQL

$ docker search mysql
NAME                              DESCRIPTION                                     STARS               OFFICIAL            AUTOMATED
mysql                             MySQL is a widely used, open-source relation…   8196                [OK]                
mariadb                           MariaDB is a community-developed fork of MyS…   2791                [OK]   
...

$ docker pull mysql

$ docker run --name yong-mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql

启动MySQL服务

1、ubuntu

sudo start mysql

2、centos

sudo service mysqld start

 

停止MySQL服务

 

1、ubuntu

sudo stop mysql

2、centos

sudo service mysqld stop

 

修改 MySQL 的管理员密码 

 

sudo mysqladmin -u root password newpassword

 

设置外网ip访问

正常情况下，mysql占用的3306端口只是在IP 127.0.0.1上监听，拒绝了其他IP的访问（通过netstat可以查看到）。
取消本地监听需要修改 my.cnf 文件：
sudo vi /etc/mysql/my.cnf
bind-address = 127.0.0.1 //找到此内容并且注释

此时如果报错：1130 - Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"

mysql -u root -p

输入密码之后

mysql> use mysql;

mysql> select host, user from user;

查出来的结果是这样的

执行

mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;

这时是这样的

此时，应该删除所有user为root而host不是%的行

mysql> delete from user where user='root' and host <>'%';

mysql> select host, user from user;

此时是这样的

此时就可以从外面用ip+端口号连进来了。

 

CentOS上，初次安装MySql，设置管理员（root）密码

参照：http://blog.sina.com.cn/s/blog_8442befd01015zld.html

1、停止服务器

sudo service mysqld stop

2、进入安全模式

sudo mysqld_safe --skip-grant-tables & mysql -u root -p

3、修改root密码

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 password=PASSWORD("你的新密码")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)


mysql> quit
Bye

4、进入数据库

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution


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

Bye

出现“ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.”

mysql> SET PASSWORD = PASSWORD('new_pwd');
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

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