MySQL

From PersoWiki
Jump to: navigation, search

Useful MySQL commands

How to set MySQL Root password

# mysqladmin -u root password YOURNEWPASSWORD

How to Change MySQL Root password

mysqladmin -u root -p123456 password 'xyz123'

How to Find out current Status of MySQL server

# mysqladmin -u root -ptmppassword status
Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

How to check status of all MySQL Server Variable’s and value’s

# mysqladmin -u root -p extended-status

Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

How to see all MySQL server Variables and Values

# mysqladmin  -u root -p variables

Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

How to check all the running Process of MySQL server

# mysqladmin -u root -p processlist

Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

How to create a Database in MySQL server

# mysqladmin -u root -p create databasename

How to drop a Database in MySQL server

# mysqladmin -u root -p drop databasename

How to reload/refresh MySQL Privileges

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

How to kill MySQL Client Process

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |					 |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

How to start/stop MySQL replication on a slave server

# mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

Users and DBA’s (Database Admins) create, delete, manipulate

Note: To create, delete or alter a DBA you'll first need to be logged in as the root mysql DBA or a DBA with full access.

Show all users in the mysql database:

mysql> SELECT * FROM mysql.user;

Delete a null user:

mysql> DELETE FROM mysql.user WHERE user = ' ';

Delete all users who are not root:

mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Change root user to a less-guessable name (for security):

mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Create a new DBA:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Add a new user with specific privileges on a specific database:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Add a new DBA with all privileges to a specific database:

mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Change a user or DBA password:

mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Delete a user or DBA from the database:

mysql> DELETE FROM mysql.user WHERE user="username";


Simple database manipulation

Show all databases:

mysql> SHOW DATABASES;

Create a database:

mysql> CREATE DATABASE mydatabase;

Use a specific database:

mysql> USE mydatabase;

Delete a database:

mysql> DROP DATABASE mydatabase;

Optimize a database: All Databases:

$ sudo mysqlcheck -o --all-databases -u root -p

Single Database:

$ sudo mysqlcheck -o db_schema_name -u root -p

Rename a database from command line (not from within MySQL), there is purposely no space beween -p and mypass:

mysqldump -u dbauser -pmypass databasename > mybackup.sql
mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":

mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;


Simple table manipulation

Show all tables within a previously selected database

mysql> SHOW TABLES;

Show data within a specific table in a previously selected database:

mysql> SELECT * FROM tablename;

Rename a table within a specific database:

mysql> RENAME TABLE first TO second;

or

mysql> ALTER TABLE mytable rename as mynewtable;

Drop a table from a specific database:

mysql> DROP TABLE mytable;


Simple column and field manipulation

Show columns within a table:

mysql> DESC mytable;

or

mysql> SHOW COLUMNS FROM mytable;

Update a field within a previously selected database table:

mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Select data within a specific table in a previously selected database:

mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Insert data into a table and its columns within a specific database:

mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Delete data from specific fields within a column:

mysql> DELETE FROM mytable WHERE mycolumn="mydata";

Update data in a specific table, column, field:

mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";


Simple backup options

Backup all databases uncompressed from the command line (not from within MySQL):

mysqldump -u root -pmypass --all-databases > alldatabases.sql

restore full:

mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

restore single:

mysql -u username -pmypass mydb < mydb.sql (no space in between -p and mypass)

Backup all databases compressed from the command line (not from within MySQL): With bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (use bunzip2 to uncompress)

With gzip:

mysqldump --all-databases | gzip > databasebackup.sql.gz (use gunzip to uncompress)

Backup a specific database only:

mysqldump -u username -pmypass databasename > backupfile.sql

Backup database structure only:

mysqldump --no-data --databases databasename > structurebackup.sql

Backup a specific database and specific tables within that database only:

mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql