MySQL

How to reset MySQL root password

Categories: Database; Tagged with: MySQL; @ May 1st, 2016 15:27

I lost root’s password, again.

stop MySQL

we need to stop mysql and start it in safe mode. I tried many ways to kill mysql, but only this works for my Ubuntu 14.04:

root@myhost:/usr/bin# initctl --system stop mysql

start MySQL in safe mode, set new password for root

sudo mysqld_safe --skip-grant-tables &
mysql -uroot
use mysql;
update user set password=PASSWORD("new_password") where User='root';
flush privileges;
quit
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

you may try to login using the new password now:

mysql -u root -p

store/update MySQL password in ~/.my.cnf

if you’re using a password file, remember to update it.

[client]
user=root 
password=new_password

useful links
http://stackoverflow.com/questions/18733944/ubuntu-cant-stop-mysqld
https://www.digitalocean.com/community/questions/setup-mysql-on-ubuntu-droplet-getting-error-error-1045-28000-access-denied-for-user-root-localhost-using-password-yes

WordPress: batch deleting spam comments 批量删除垃圾回复

Categories: Development Notes • WordPress; Tagged with: MySQL • SQL • WordPress; @ March 16th, 2013 10:50

Use sql to delete all the spam comments quickly:

Status of comment

SELECT DISTINCT comment_approved  FROM `x_comments`

Results:

0 – Pending

1 – approved

spam – spam

trash – trash

Deleting SQL

–delete spam & trash:

DELETE FROM `dbName`.`x_comments` WHERE comment_approved = 'spam' or comment_approved = 'trash'

--delete pending:
DELETE FROM `dbName`.`x_comments` WHERE comment_approved = '0'

MySql: 使用数据库文件恢复

Categories: Database; Tagged with: MySQL • 数据恢复; @ September 16th, 2010 21:43

需求: 在没有.sql备份文件的情况下, 使用mySql文件恢复数据库

解决方法:

1. 安装同版本的MySql – 查看之前的MySql readme文件获知版本
2. 找到Mysql的数据库文件存放位置. 有很多名称同数据库名称的文件夹,
2.1复制要恢复的数据库对应的文件夹到新的mySql的数据库文件目录中. (如果找不到, 查阅my.ini中的配置信息). 文件夹内存放了Database的结构.
2.2 从旧的Mysql目录中复制 ibdata1文件到新目录中(该文件存储具体数据, 大小随着视数据库内容而变)
4. 启动新安装的MySql, 之前的数据库已完整恢复.

(more…)

Reset Mysql Root Password in XAMPP 在XAMPP中重置MySql密码

Categories: Database; Tagged with: MySQL • XAMPP; @ September 1st, 2010 21:15

默认情况下, 通过XAMPP安装的Mysql Root密码为空.

在XAMPP中重置Mysql密码:

1. Go to http://localhost:port/security/ 该链接将会对目前的配置进行安全检查

2. 该页面会检查当前安全配置, 并会带有链接: http://localhost/security/xamppsecurity.php

3. 该页面中可进行重置密码等操作.

See more : http://veerasundar.com/blog/2009/01/how-to-change-the-root-password-for-mysql-in-xampp/

MySql: backup and restore database using mysql/mysqldump

Categories: Database; Tagged with: CMD • MySQL • MySQLDump; @ July 31st, 2010 16:21

Backup database:

> (sudo) mysqldump db-name > db-backup.sql

Restore database:

> (sudo) mysql db-name (--no-date)(no-create-info) < db-backup.sql

Older Posts