Riccardo Riva

Posts Tagged ‘MySQL’

The following is a list of MySQL useful commands.

# To setup root password (first execution)
mysqladmin -u root password 'new_password'

# To login to MySQL
mysql -u root -p

# To create a database
create database dbname;

# To change database
use dbname;

# To create a user and assign to it permission to database
# Grant permission only from localhost connections
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Grant permission on all connections
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Manage MySQL export
# Copy all db1 content to /backup-db/db1 folder
mysqlhotcopy db1 /backup-db/db1

# Create a DB dump to a file
mysqldump db1 > db1_dump_db.sql -u root -p

Hope this help

Bye
Riccardo

Bookmark It

Recover MySQL database root password

Sometimes you couls have to recover MySQL database server password.
You could do it follow these easy steps :

1: Stop the MySQL server process.

/etc/init.d/mysql stop

2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password

mysqld_safe –skip-grant-tables &

3: Connect to mysql server as the root user

mysql -u root

4: Setup new root password

mysql> use mysql;
mysql> update user set password=PASSWORD(”NEW-ROOT-PASSWORD“) where User=’root’;
mysql> flush privileges;
mysql> quit

5: Exit and restart MySQL server

/etc/init.d/mysql stop

6: Start MySQL server and test it

/etc/init.d/mysql start
mysql -u root -p

Hope this help

Bye
Riccardo

Bookmark It