Skip to content

Tag Archives: password

MySQL – Help, I lost the Root password! Error number 1045

Tuesday, September 7, 2010

Have you ever seen the dreaded MySQL Error number 1045 Access denied for user ‘root’@'localhost’ (using password: YES)?

Don’t worry, this is an easy to recover from situation. Just follow these basic steps to reset the root user password.

Stop the MySQL server process.

Start the MySQL server process with the –skip-grant-tables option. This option causes the server to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. BEWARE! Anyone can access your server, so make sure off the Internet, and that you perform the following steps as quickly as possible.

Start the MySQL console client with the -u root option. Mysql -u root

SELECT * FROM mysql.user;

UPDATE mysql.user SET Password=PASSWORD(‘[password]‘) WHERE User=’[username]‘; Replace [username] with root to change root. You can also change any other user as well.

Stop the MySQL process

Start the MySQL Process normally (i.e. without the –skip-grant-tables option).

Some folks have reported that issuing a Flush Privileges command will prevent your having to perform the final Stop/Start of the MySQL server. However, this has not always worked for me, and I think to be safe, it is wise to recycle the server.

Filed in Errors, MySQL | Also tagged , , , , , , , , | Comments (0)

MySQL- Allowing Access to Root Remotely

Thursday, August 19, 2010

MySQL includes all the basic methods needed to secure your user accounts.  However, the syntax and style used to manage it is often confusing to MySQL novices.  Here are a couple tips to get you started, however I strongly suggest your read the MySQL User Account Management documentation.

If you need to change your password for your localhost root account, here is the proper syntax.

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST' = PASSWORD('new_password');

You can also grant access to all remote servers as well by using the following syntax.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

By default, MySQL only allows access by the ‘root’ account from the host running the database server (‘localhost’).

The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

If you are setting up your server and require NO remote access, add skip-networking to your my.cnf file.

skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

 

Filed in MySQL | Also tagged , , , , , , , , , , , , , | Comments (0)

MySQL – Restoring from a Dump Syntax

Friday, March 26, 2010

Here’s how to pass the arguments to MySQL to restore from a Dump:

mysql [database name] < [backup file name]

The “[database name]” specifies the name of the database to restore, and “[backup file name]” specifies the full path and filename to the file generated by mysqldump, such as backup.sql. You should also specify your MySQL username and password as usual using -uroot -ppassword.

If I wanted to restore the database named maindatabase from the file named backup.sql, I would run:

mysql maindatabase< backup.sql

Filed in MySQL, Restore Backup | Also tagged , , , , , , , , , , | Comments (0)