Resetting a forgotten MySQL root
password
root@steve:~# /etc/init.d/mysqd stop
root@steve:~# /usr/bin/mysqld_safe --skip-grant-tables &
[1] 6702
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6763]: started
root@steve:~$ mysql --user=root mysql
Enter password:
mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
root@steve:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
$
mysqladmin -u root password NEWPASSWORD
$
mysqladmin -u root -p'oldpassword' password newpass
$ mysqladmin -u root -p'abc' password '123456'
$
mysqladmin -u vivek -p oldpassword password newpass
MySql
Commands:
Logging
In
$ mysql -u <username> -p
Enter password:
Create
database
mysql> CREATE DATABASE <database>;
mysql> CREATE DATABASE vworks;
We can now
check for the presence of this database by typing:
mysql> SHOW DATABASES;
+-----------+
| Database |
+-----------+
| mysql |
| vworks |
+-----------+
2 rows in set (0.06 sec)
Deleting
or DROPing a database
DROP DATABASE <database>
Granting
Privileges on the new database
GRANT <privileges>
ON <database>
TO <user>
[IDENTIFIED BY <password>]
[WITH GRANT OPTION]
mysql> GRANT ALL PRIVILEGES
-> ON vworks.*
-> TO newuser@localhost
-> IDENTIFIED BY 'newpassword';
Revoking
privileges
mysql> REVOKE ALL PRIVILEGES
-> ON vworks.*
-> FROM badvworks@localhost;
mysqlhotcopy
mysqlhotcopy -u <username> -p <database> /backup/location/
mysqldump
mysqldump -u <username> -p <database> [<table>] > file.sql
mysqldump -u admin -p vworks > vworks.sql
Restoring
a Dump
$ mysql -u admin -p vworks < vworks.sql
$ mysql -u admin -p vworks < artist.sql
$ mysql -u admin -p < vworksDB.sql
Optimising
a dump
$ mysqldump -u admin -p --opt vworks > vworks.sql
$ mysqldump -u admin -p --databases vworks | \
> mysql -u backup -p MyPassword -h remote.server.com
mysql> CREATE DATABASE vworks2;
Remote
Client Connection
$ mysql -u <username> -p -h <host>
For
example to connect to a fictional vworks.keithjbrown.co.uk server:
$ mysql -u admin -p -h vworks.keithjbrown.co.uk
Non-Interactive
Commands
$ mysql -u admin -p vworks -e 'SELECT cds.artist, cds.title FROM cds'
Enter password:
+------------+------------------------------+
| artist | title |
+------------+------------------------------+
| Jamiroquai | A Funk Odyssey |
| Various | Now 49 |
| westlife | westlife |
| Various | Eurovision Song contest 2001 |
| Abba | Abbas Greatest Hits |
1.
How to change the MySQL root user password?
# mysqladmin -u root -ptmppassword password 'newpassword'
# mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2. How to check whether MySQL Server is up and
running?
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
3. How do I find out what version of MySQL I am running?
Apart from giving the ‘Server version’, this command also
displays the current status of the mysql server.
# mysqladmin -u root -ptmppassword version
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 107 days 6 hours 11 min 44 sec
Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. What is the current status of MySQL server?
# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
The status command displays the following information:
Uptime: Uptime of the mysql server
in seconds
Threads: Total number of clients
connected to the server.
Questions: Total number of queries
the server has executed since the startup.
Slow queries: Total number of
queries whose execution time waas more than long_query_time
variable’s value.
Opens: Total number of tables
opened by the server.
Flush tables: How many times the
tables were flushed.
- Open tables: Total number of open tables in the database.
5. How to view all the MySQL Server status
variable and it’s current value?
# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_admin_commands | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
6. How to display all MySQL server system variables and the values?
# mysqladmin -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| auto_increment_increment | 1 |
| basedir | / |
| big_tables | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
skip.....
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmpdir | /tmp |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.25-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------+
7. How to display all the running process/queries in the mysql
database?
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
You can use this command effectively to debug any performance issue
and identify the query that is causing problems, by running the
command automatically every 1 second as shown below.
# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
8. How to create a MySQL Database?
# mysqladmin -u root -ptmppassword create testdb
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
Note: To display all tables in a database,
total number of columns, row, column types, indexes etc., use the
mysqlshow
command that we discussed in our previous
articles.
9. How to Delete/Drop an existing MySQL database?
# mysqladmin -u root -ptmppassword drop testdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'testdb' database [y/N] y
Database "testdb" dropped
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
+--------------------+
3 rows in set (0.00 sec)
1. How to
change the MySQL root user password?
# mysqladmin -u root -ptmppassword password 'newpassword'
# mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2. How to check whether MySQL Server is up
and running?
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
3. How do I find out what version of MySQL I am running?
Apart from giving the ‘Server version’, this command also
displays the current status of the mysql server.
# mysqladmin -u root -ptmppassword version
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 107 days 6 hours 11 min 44 sec
Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. What is the current status of MySQL server?
# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
The status command displays the following information:
Uptime: Uptime of the mysql server
in seconds
Threads: Total number of clients
connected to the server.
Questions: Total number of queries
the server has executed since the startup.
Slow queries: Total number of
queries whose execution time waas more than long_query_time
variable’s value.
Opens: Total number of tables
opened by the server.
Flush tables: How many times the
tables were flushed.
- Open tables: Total number of open tables in the database.
5. How to view all the MySQL Server status
variable and it’s current value?
# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_admin_commands | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
6. How to display all MySQL server system variables and the values?
# mysqladmin -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| auto_increment_increment | 1 |
| basedir | / |
| big_tables | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
skip.....
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmpdir | /tmp |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.25-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------+
7. How to display all the running process/queries in the mysql
database?
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
You can use this command effectively to debug any performance issue
and identify the query that is causing problems, by running the
command automatically every 1 second as shown below.
# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
8. How to create a MySQL Database?
# mysqladmin -u root -ptmppassword create testdb
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
Note: To display all tables in a database,
total number of columns, row, column types, indexes etc., use the
mysqlshow
command that we discussed in our previous
articles.
9. How to Delete/Drop an existing MySQL database?
# mysqladmin -u root -ptmppassword drop testdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'testdb' database [y/N] y
Database "testdb" dropped
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
+--------------------+
3 rows in set (0.00 sec)
1. How to
change the MySQL root user password?
# mysqladmin -u root -ptmppassword password 'newpassword'
# mysql -u root -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2. How to check whether MySQL Server is up
and running?
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
3. How do I find out what version of MySQL I am running?
Apart from giving the ‘Server version’, this command also
displays the current status of the mysql server.
# mysqladmin -u root -ptmppassword version
mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.25-rc-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 107 days 6 hours 11 min 44 sec
Threads: 1 Questions: 231976 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. What is the current status of MySQL server?
# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
The status command displays the following information:
Uptime: Uptime of the mysql server
in seconds
Threads: Total number of clients
connected to the server.
Questions: Total number of queries
the server has executed since the startup.
Slow queries: Total number of
queries whose execution time waas more than long_query_time
variable’s value.
Opens: Total number of tables
opened by the server.
Flush tables: How many times the
tables were flushed.
- Open tables: Total number of open tables in the database.
5. How to view all the MySQL Server status
variable and it’s current value?
# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 579 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 41387238 |
| Bytes_sent | 308401407 |
| Com_admin_commands | 3524 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
6. How to display all MySQL server system variables and the values?
# mysqladmin -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| auto_increment_increment | 1 |
| basedir | / |
| big_tables | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
skip.....
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmpdir | /tmp |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.25-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------+
7. How to display all the running process/queries in the mysql
database?
# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
You can use this command effectively to debug any performance issue
and identify the query that is causing problems, by running the
command automatically every 1 second as shown below.
# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost | | Sleep | 36 | | |
| 23 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
8. How to create a MySQL Database?
# mysqladmin -u root -ptmppassword create testdb
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
Note: To display all tables in a database,
total number of columns, row, column types, indexes etc., use the
mysqlshow
command that we discussed in our previous
articles.
9. How to Delete/Drop an existing MySQL database?
# mysqladmin -u root -ptmppassword drop testdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'testdb' database [y/N] y
Database "testdb" dropped
# mysql -u root -ptmppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
+--------------------+
3 rows in set (0.00 sec)
