Monday 24 September 2012

MySql Bascis

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)

No comments:

Post a Comment