Category: mysql

Recover your MySQL password

sheepchenLeave a comment

What if you’ve forgotten your MySQL root user password? This could be quite the predicament … had the developers not thought of that eventuality. In order to recover the password, you simply have to follow these steps:

  1. Stop the current MySQL server process
  2. Start the MySQL server with the command sudo mysqld_safe –skip-grant-tables –skip-networking &
  3. Connect to the MySQL server as the root user with the command mysql -u root

At this point, you need to issue the following MySQL commands to reset the root password:

mysql> use mysql;
​mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';
​mysql> flush privileges;
​mysql> quit

After this you can shutdown the mysql-server and start it the normal way.

(!) on very old mysql databases the “authentication_string” field is called “Password”. so the command looks like follows:

​mysql> update user set Password=password('NEWPASSWORD') where user='root';


source: [link]

How to export/import MySQL database with exact character set?

sheepchenLeave a comment 
mysqldump -uUSERNAME -pPASSWORD --default-character-set=utf8 DATABASE > backup.sql

When you import backup into an empty MySQL database, you can set the exact character set for the data that will be inserted. To do this, use the following command:

mysql -uUSERNAME -pPASSWORD --default-character-set=utf8 DATABASE < backup.sql

Replace –default-character-set=utf8 with the charset used for the creation of the backup.sql file. This will ensure that your data is inserted correctly.

character-set UTF8 vs UTF8MB4

MySQL utf8 is a propiatary standard from MySQL which does not reflect the real utf-8 standard. It uses only max. 3 bytes for a character. If you want to use real utf8 in your database you should use utf8mb4.

source: siteground.com, hydroxi.de