Archive for the ‘MySQL’ Category

MySQL password hashing

Friday, January 2nd, 2009

Whenever you upgrade your MySQL installation, make sure to upgrade any client that uses it.

In some cases, clients that use a version prior to 4.1 will have a problem authenticating against the MySQL database if the latter has a post 4.1 version.

The trick is that after 4.1 (i.e. 4.11 and up), MySQL changed the way it stores the passwords in the user table inside the mysql system database.
Password hashes are now 41 bytes long instead of the old 16 bytes.

So for example, if your MySQL server is 5.0, while your php-mysql library is 4.1, your web applications will fail to connect to the database. As such, it is recommended that you upgrade the client.

In any case, MySQL offers a way to change the hash back into the old format. For the sake of argument, assume the user in question is john, and you want to be able to connect using password dummy. In this case, connect to your MySQL server from the prompt (SSH and use ‘mysql -u root -p mysql’ on linux, or go to your mysql/bin windows directory and execute the same query), then issue the following queries:

update user set Password=OLD_PASSWORD(‘dummy’) where User=’john’;
flush privileges;

the OLD_PASSWORD() function will generate the old 16 bytes hash. The first query will eventually update the user password to use this hash. The second query is necessary in order for the MySQL service to re-read the new user privileges.

PS: if your root password is not working, refer as well to the guide on resetting it.

Alternatively, if your database has many users and you didn’t keep track of them, you can use the following query and it will return usernames that are using the new hash

select distinct(User) from User where LENGTH(Password)!=41;

Setting up MySQL database replication

Monday, September 15th, 2008

It’s always about data. What’s the use of any system without the data it relies on?

Setting up a database replication is one of many steps that should be taken in order to preserve data, preventing any loss and making disaster recovery easier.

Luckily, it’s easy with MySQL. So let’s suppose we have two servers running MySQL, one called host1 and the other host2.
Replication can be either master-master or master-slave. With a master-slave replication, the slave always replicates what the master database is executing. In master-master replication, both databases synchronize with each others.

For the purpose of this tutorial, a master-slave (here host1 and host2 respectively) scenario is examined.

First of all, open the mysql config file on host1 (usually found at /etc/my.cnf on linux, and c:\windows\my.ini on windows), and uncomment (remove the hash of) the following line:


Secondly, you need to specify the file where the master (host1) should log (write) the queries it’s executing. This will enable the slave (host2) to read these queries and execute them as well. As such, add a line such as:

log-bin = /path/to/mysql-bin.log

where the value above is the path to file where MySQL should be doing the logging. You could very well create a separate directory or use the default mysql installation directory (such as c:\program files\mysql\ on windows or /var/lib/mysql on linux)

Then, you need to specify the name of the database in question. So if you’re setting up replication for one of your MySQL databases called ‘work_data’, then, this is the line you need to add to your MySQL config:

binlog-do-db = work_data

Finally, you need to specify a server id, which says that this is the master server


Save the config file and exit.
Now you need to give host2 the permission to replicate the data. As such, a MySQL query needs to be issued on the master.

So on host1, login to the MySQL prompt (mysql -u root -pyour_root_password) (or PHPMyAdmin, etc… whatever you use), and issue the following statement:

grant replication slave on *.* to ‘username’@’%’ identified by ‘password’;

Make sure to replace username and password with a credential of your choice. Do keep the single quotes though.
The % sign means that the slave can connect from any host. If you want it to be more secure, replace that with host2 (the slave’s hostname).

After all the above is done, restart the MySQL service (service mysqld restart (linux) or, net stop mysql, net start mysql (on windows)).

If the database had data earlier, make sure you dump it and load it on the slave before doing any of the above. Dumping data is easy and can be done by cd’ing to the MySQL bin directory and issuing:

mysqldump -Q -u root -pyour_root_password databasename > database_dump.sql

(replace the password and database name with the correct login). The whole database will now be in the file called database_dump.sql

To import it on host2, cd to the mysql bin directory and issue:

mysql -u root -pyour_root_password databasename < /path/to/the/file/database_dump.sql

The MySQL config file on host2 should have the following lines:

master-host = host1
master-user = username
master-password = password
master-port = 3306

where host1 is the master’s hostname/IP, and username and password are the credentials you used when granting replication access a few steps above. 3306 is the port MySQL is running on (which is the default)

Then start the slave process on host2 by issuing at the MySQL prompt:

start slave;

To make sure replication is working, issue the following SQL query on host1:

show slave status \G

(Slave_SQL_Running and Slave_IO_Running should report ‘Yes’)

Good luck

Resetting your mysql root password

Thursday, May 17th, 2007

It is quite frequent that an administrator simply forgets his mysql’s root password.

Luckily, it is quiet easy to reset it, here are the steps:

  1. SSH as root to your machine
  2. Turn off the mysqld daemon if running
    • RedHat/Fedora users can do so by executing:  service mysqld stop
  3. Run safe_mysqld by executing:
    • safe_mysqld –skip-grant-tables
      (this will run allow you to connect without a password)
  4. Open a second shell / SSH again and execute:
    • mysql mysql
      (to directly connect and select the mysql database which contains the user authentication data)
  5. On the mysql prompt, execute:
    • update user set password=password(‘newpassword’) where user=’root’;
      where newpassword is your newly chosen password.
  6. That’s it! close everything and start your mysql daemon again:
    • service mysqld start

How to import a csv file into mysql from the prompt

Thursday, January 11th, 2007

For the follows out there who do not know how to import coma separated data into mysql from the prompt, here it is:


PATH is the path to the csv file (can be relative to the directory you were in before logging into mysql)
TABLE is the table name that you have already created and whos fields match the csv file fields.
USERNAME is the mysql user that have access to insert data into TABLE
DATABASE is the database containing that table

- login to mysql by typing: mysql -u USERNAME -p DATABASE
The prompt will ask you for the user password, type it.

- Execute the following query:

load data local infile ‘PATH’ into table TABLE fields terminated by ‘,’ lines terminated by ‘\n’;

And voila.