Category: MySQL

mysqldump备份时出现问题

使用mysqldump备份时，–opt表示采用优化(Optimize)方式

mysqldump --opt -uroot -ppasswd c14sql >c14sql.sql

出现错误

mysqldump: Got error: 145: Table './c14sql/is_stats' is marked as crashed and should be repaired when using LOCK TABLES

解决办法1，用myisamchk

myisamchk -c -r /var/lib/mysql/c14sql/is_stats.MYI

解决办法2，进入mysql>（暂未测试）

mysql> repair table is_stats;

检测一下

mysql> check tables is_stats;

没问题的话再备份就没有问题了

ps:

mysqldump 导出表结构 –no-data flag to make it only export structure

mysqldump -u jira -p -h 192.168.9.11 jira6 --no-data > jira6.nodata.sql

2012 年 3 月 29 日

mysql（已知旧密码）修改密码的两种方法
http://www.cyberciti.biz/faq/mysql-change-root-password/

这里的前提是你能登的进mysql

方法一：用mysqladmin命令修改root密码

如果你装mysql的时候用的空密码，现在要设定一个初始密码，那么执行：If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:
```
$ mysqladmin -u root password NEWPASSWORD
```
你想要更新密码，用如下命令，However, if you want to change (or update) a root password, then you need to use the following command:
```
$ mysqladmin -u root -p'oldpassword' password newpass
```
例如，旧密码是abc，新密码是123456，则输入，For example, If the old password is abc, you can set the new password to 123456, enter:
```
$ mysqladmin -u root -p'abc' password '123456'
```
更改其他用户的密码，Change MySQL password for other users

如更改普通用户密码，用如下命令，To change a normal user password you need to type (let us assume you would like to change password for user vivek) the following command:
```
$ mysqladmin -u vivek -p oldpassword password newpass
```
方法二：用mysql的sql语言来更改root密码

Changing MySQL root user password using MySQL sql command

在mysql数据库中保存了root的密码，This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:

1)用root登陆 Login to mysql server, type the following command at shell prompt:
```
$ mysql -u root -p
```
2) 切换到mysql数据库，Use mysql database (type command at mysql> prompt):
```
mysql> use mysql;
```
3) 更新vivek的密码Change password for user vivek, enter:
```
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';
```
4) 最后刷新权限，Finally, reload the privileges:
```
mysql> flush privileges;
mysql> quit
```
后一种方法可以用在php,python和perl中
The last method can be used with PHP, Python or Perl scripting mysql API.

2014-01-09更新：

执行SET PASSWORD命令，更改 ‘jpuyy’@’localhost’
```
SET PASSWORD FOR 'jpuyy'@'localhost' = PASSWORD('password');
```
如果是更改自己的密码，可省略for语句
```
SET PASSWORD = PASSWORD('password');
```
还可以使用GRANT USAGE(在*.*)来指定某个账号的密码而不影响账户当前的权限。
```
GRANT USAGE ON *.* TO 'jpuyy'@'localhost' IDENTIFIED BY 'password';
```
2012 年 3 月 29 日

mysql启动失败

/usr/sbin/mysqld status

提示

lc-messages-dir=/usr/share/mysql

在http://www.dotdeb.org/2011/11/01/mysql-5-5-is-finally-here/ 给出了解决方法

在my.cnf中注释掉就可以了

"The mysql-common package will be upgraded to version 5.5.17. It’s no
big deal, it only contains retro-compatible configuration files."
Wrong. The my.cnf delivered by mysql-common-5.5 contains the
lc-messages-dir setting, which MySQL Server 5.1 cannot understand
and will fail to start.To everyone who upgraded mysql-common to 5.5,
but left mysql-server unchanged: Comment out the lc-messages-dir
setting in my.cnf OR YOUR MYSQL SERVER WILL FAIL TO START.

 Best regards,
 Tobias

2011 年 11 月 20 日

mysql创建用户并控制其对数据库、表、列的权限

创建一个用户realabc，步骤为进入mysql库，向user表中插数据，本地登陆，密码abc；flush privileges 使配置生效

mysql> use mysql;
mysql> insert into user (Host,User,Password) values ("localhost","realabc",PASSWORD("abc"));
mysql> flush privileges;

其实直接使用grant命令mysql也会帮我们创建用户

grant all privileges on *.* to 'yyy'@'localhost' identified by '123';

接下来想让realabc用户来插入、更新discuzx数据库里的pre_abc、pre_def表，那么肯定是用insert,update,当然还有select，要不数据都没法看到。先查看当前用户的权根：

mysql> show grants for realabc@localhost;
 +----------------------------------------------------------------------------------------------------------------+
 | Grants for realabc@localhost                                                                                   |
 +----------------------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'realabc'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' |
 +----------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

接下来分两步输命令，提升权根

GRANT SELECT, INSERT, UPDATE ON `discuzx`.`pre_abc` TO 'realabc'@'localhost' ;
GRANT SELECT, INSERT, UPDATE ON `discuzx`.`pre_def` TO 'realabc'@'localhost' ;

如果权限加多了，可以使用revoke来回收权限，如

revoke select ON database1.* from 'yyy'@'localhost' ;

revoke ALL ON *.* from 'yyy'@'localhost' ;

如果为某数据库提升所有权限，使用

grant all on wordpress.* to wordpress@localhost;

这样查看权根，就会发现提升了，进phpmyadmin里就可以插数据了。

进了phpmyadmin发现还有一个information_schema，不想让phpmyadmin显示

找到phpmyadmin/libraries/config.default.php

在242行处加入information_schema

$cfg['Servers'][$i]['hide_db'] = 'information_schema';

Update: 2017-06-27
上述都是通过 grant 创建用户，在 mysql 5.7 会报 warning

+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+

使用指导的方式创建 reporting 库和对应权限

CREATE DATABASE `reporting` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
create reporting@'%' identified by '';                                          
grant all privileges on reporting.* to reporting@'%';

:)EOT

2011 年 11 月 5 日

mysql 命令 | sql语句 | sql语法

0、登陆：

mysql -u root -p

指定用utf8来连接数据库，大部分时候就不会显示乱码

mysql -u root -p --default-character-set=utf8

1、显示数据库列表。

 show databases;

2、显示库中的数据表：

 use mysql;
 show tables;

3、显示数据表的结构：

 describe 表名;

4、建库：

 create database 库名;

create database if not exists 库名 default charset utf8 collate utf8_general_ci;

5、建表：

 use 库名；
 create table 表名 (字段设定列表)；

6、删库和删表:

 drop database 库名;
 drop table 表名；

7、将表中记录清空：

 delete from 表名;

8、显示表中的记录：

 select * from 表名

9、update语法

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
当有多个列名称要修改的时候用"，"分隔。

例：把radius某用户设为禁止

UPDATE  `radusergroup` SET `groupname`='daloRADIUS-Disabled-Users'  WHERE `username` in ('xxyy ','uuxx');

10、刷新权限：

flush privileges;

11、改变数据库databaseA的字符集为utf8

ALTER DATABASE `databaseA` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

退出：

mysql>quit

12、limit命令

取出前5条数据

SELECT * FROM `user_data` LIMIT 5

取出第5, 6条数据

SELECT * FROM `user_data` limit 3,1

13、like命令

查找以189开头的条目

SELECT * from shoujihao WHERE number LIKE '189%';

14、shell下执行sql语句

mysql -u root -p database -e "select User, Host From user"

在mysql命令行内执行系统命令

mysql> system less /home/jpuyy/.vimrc

删除一个用户

drop user 'yyy'@'localhost';

14. SELECT DISTINCT筛选唯一的列，如

SELECT DISTINCT vend_id FROM products;

会返回去重的结果

15. 创建唯一键

ALTER TABLE host add UNIQUE (object_id);
16. 一些函数

select count(column_name) 计数
select sum(column_name) 总计
select avg(column_name) 平均数
select max(column_name) 最大
select min(column_name) 最小

16. 每个分组大于1

select genre, sum(cost) from Movies Group By genre Having count(*)>1;

2011 年 6 月 3 日

Category: MySQL

mysqldump备份时出现问题

mysql（已知旧密码）修改密码的两种方法

方法一：用mysqladmin命令修改root密码

方法二：用mysql的sql语言来更改root密码

mysql启动失败

mysql创建用户并控制其对数据库、表、列的权限

mysql 命令 | sql语句 | sql语法