mysql使用 - zbunix/building GitHub Wiki

语法

每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；

启动

net start mySql;

进入

mysql -u root -p
mysql -h localhost -u root -p databaseName;

查询时间

select now();

查询当前用户

select user();

查询数据库版本

select version();

查询当前使用的数据库

select database();

列出数据库

show databases;

列出表格

show tables；

显示表格列的属性

show columns from tableName；

选择数据库

use databaseName;

将数据库test备份

  mysqldump -u root -p test > test.sql

将备份数据导入到数据库test

  mysql -u root -p test < test.sql

备份表格：(备份test数据库下的mytable表格)

  mysqldump -u root -p test mytable > test_mytable.sql

清mysql库root密码

root@android-work2:/opt/redmine-1.3.2-0# ./ctlscript.sh stop mysql
121107 13:37:42 mysqld_safe Logging to '/opt/redmine-1.3.2-0/mysql/data/mysqld.log'.
121107 13:37:42 mysqld_safe Starting mysqld.bin daemon with databases from /opt/redmine-1.3.2-0/mysql/data

root@android-work2:/opt/redmine-1.3.2-0# vi  mysql/my.cnf
[mysqld]
basedir=/opt/redmine-1.3.2-0/mysql
bind-address=127.0.0.1
character-set-server=UTF8
collation-server=utf8_general_ci
datadir=/opt/redmine-1.3.2-0/mysql/data
port=3306
socket=/opt/redmine-1.3.2-0/mysql/tmp/mysql.sock
tmpdir=/opt/redmine-1.3.2-0/mysql/tmp
+skip-grant-tables

/opt/redmine-1.3.2-0/mysql/scripts/ctl.sh : mysql  started at port 3306

root@android-work2:/opt/redmine-1.3.2-0# ./mysql/bin/mysqladmin -u root flush-privileges  password "nwd123456"
./mysql/bin/mysqladmin.bin: unable to change password; error: 'Can't find any matching row in the user table'

root@android-work2:/opt/redmine-1.3.2-0# ./ctlscript.sh stop mysql

root@android-work2:/opt/redmine-1.3.2-0# vi  mysql/my.cnf
[mysqld]
basedir=/opt/redmine-1.3.2-0/mysql
bind-address=127.0.0.1
character-set-server=UTF8
collation-server=utf8_general_ci
datadir=/opt/redmine-1.3.2-0/mysql/data
port=3306
socket=/opt/redmine-1.3.2-0/mysql/tmp/mysql.sock
tmpdir=/opt/redmine-1.3.2-0/mysql/tmp
-skip-grant-tables

测试 root@android-work2:/opt/redmine-1.3.2-0# ./mysql/bin/mysql -uroot -pnwd123456 mysql
修改mysql用户密码

　　mysql -u root mysql  
　　mysql> Update user SET password=PASSWORD("new password") Where user=’name’;  
　　mysql> FLUSH PRIVILEGES;  
　　mysql> QUIT

查看有那些数据库

root@android-work2:/opt/redmine-1.3.2-0# ./mysql/bin/mysql -uroot -pnwd123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bitnami_redmine    |
| mysql              |
| performance_schema |
| test               |
+--------------------+

增加远程root用户访问权限

/opt/redmine-1.3.2-0/mysql/bin/mysql -uroot -pnwd123456 -e "show databases;use mysql;GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

/opt/redmine-1.3.2-0$ ./mysql/bin/mysql -uroot -pnwd123456 -e "show databases;use mysql;select host,user from user where user='root';"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bitnami_redmine    |
| mysql              |
| performance_schema |
| test               |
+--------------------+
+---------------+------+
| host          | user |
+---------------+------+
| %             | root |
| 127.0.0.1     | root |
| ::1           | root |
| android-work1 | root |
| localhost     | root |
+---------------+------+

安全模式进入root

首先，将mysql服务停止
#/etc/init.d/mysql stop   或者  #sudo service mysqld stop
然后再执行
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
记住  后面那个  &是需要的
等出现一些提示后  应该是在starting...之后
直接输入  mysql 即可进入数据库了  (这里其实也可以是忘记密码后进行修改密码的方法)
进入后，修改密码的语句为：
mysql> use mysql;   
进入数据库修改密码
mysql> update user set password = PASSWORD('你的新密码') where user='root'
如果是要解决上面修改了host的方法就是执行
mysql> update user set host='localhost' where user='root' and host='%'
将host改回来

查看了下数据库的host信息

mysql> select host from user where user = 'root';

增加远程root

CREATE USER 'root'@'%' IDENTIFIED BY  '***';

GRANT ALL PRIVILEGES ON * . * TO  'root'@'%' IDENTIFIED BY  '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

mysql使用 - zbunix/building GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️