In Focus

Important Facts About MySQL Server

In this article we will learn some of the important facts about MySQL Server.

If you have a fresh installation of MySQL server, then it doesn’t require a password to connect as the root user. To set the MySQL password for the root user, use the following command.

  1. # mysqladmin -u root password YOURNEWPASSWORD  
How to Change MySQL Root password

If you would like to change or update the MySQL root password, then you need to use the following command. For example, say your old password is 123456 and you want to change it to a new password, say xyz123. Use the following:

mysqladmin -u root -p123456 password 'xyz123'.

How to determine if MySQL Server is running

To determine whether the MySQL server is up and running, use the following command.
  1. # mysqladmin -u root -p ping  
Enter password:
  1. mysqld is alive  
How to determine which MySQL version I am running

The following command shows the MySQL version along with the current running status.
  1. # mysqladmin -u root -p version  
Enter password:

mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.5.28
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 7 days 14 min 45 sec

Threads: 2 Questions: 36002 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059

How to determine current Status of MySQL server

To determine the current status of the MySQL server, use the following command. The mysqladmin command shows the status of uptime with the running threads and queries.

  1. # 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 determine the status of all MySQL Server Variables and values

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.
  1. # 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

To see all the running variables and values of MySQL server, use the following command.

  1. # 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

The following command will display all the running process of MySQL database queries.

  1. # mysqladmin -u root -p processlist  
Enter password:

Id Info User Host db Command Time State
18001 rsyslog localhost:38307 rsyslog Sleep 5590  
18020 root localhost   Query 0  
Show process List            

How to create a Database in MySQL server

To create a new database in MySQL server, use the command as shown below.

  1. # mysqladmin -u root -p create databasename  
Enter password:
  1. # mysql -u root -p  
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  1. mysql> show databases;  
Database
information_schema
databasename
mysql
test

8 rows in set (0.01 sec)

mysql>

How to drop a database in MySQL server

To drop a database in MySQL server, use the following command. You will be asked to confirm by pressing ‘y‘.

# mysqladmin -u root -p drop databasename

Enter password:

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 'databasename' database [y/N] y
Database "databasename" dropped.

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 shutdown MySQL server Safely

To shutdown MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:

You can also use the following commands to start/stop MySQL server.

  1. # /etc/init.d/mysqld stop  
  2. # /etc/init.d/mysqld start  
Some useful MySQL Flush commands

The following are some useful flush commands with their description.
  1. flush-hosts: Flush all host information from host cache.
  2. flush-tables: Flush all tables.
  3. flush-threads: Flush all threads cache.
  4. flush-logs: Flush all information logs.
  5. flush-privileges: Reload the grant tables (same as reload).
  6. flush-status: Clear status variables.
  1. # mysqladmin -u root -p flush-hosts  
  2. # mysqladmin -u root -p flush-tables  
  3. # mysqladmin -u root -p flush-threads  
  4. # mysqladmin -u root -p flush-logs  
  5. # mysqladmin -u root -p flush-privileges  
  6. # mysqladmin -u root -p flush-status  
How to kill Sleeping MySQL Client Process

Use the following command to identify a sleeping MySQL client process.
  1. # mysqladmin -u root -p processlist  
Enter password:

Id User Host db Command Time States Info
5 root localhost   sleep 14   show processlist
8 root localhost   Query 0    

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

  1. # mysqladmin -u root -p kill 5  
Enter password:

Id User Host db Command Time States Info
12 root localhost   Query 0   show processlist

If you like to kill multiple processes then pass the process IDs separated by commas as shown below.

  1. # mysqladmin -u root -p kill 5,10  
How to run multiple mysqladmin commands together

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.
  1. # mysqladmin -u root -p processlist status version  
Enter password:

 

Id User Host db Command Time States Info
8 root localhost   Query 0   show processlist

Uptime: 3801 Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003

mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Server version 5.5.28
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 3 min 21 sec

Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003

16. How to connect to remote MySQL server

To connect to a remote MySQL server, use the -h (host) with IP Address of remote machine.

  1. # mysqladmin -h 172.16.25.126 -u root -p  
How to execute command on remote MySQL server

Let’s say you would like to see the status of a remote MySQL server, then the command would be:
  1. # mysqladmin -h 172.16.25.126 -u root -p status  
How to start/stop MySQL replication on a slave server

To start/stop MySQL replication on the same server, use the following commands.
  1. # mysqladmin -u root -p start-slave  
  2. # mysqladmin -u root -p stop-slave  
How to store MySQL server Debug Information to logs

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about the event scheduler.
  1. # mysqladmin -u root -p debug  
Enter password:

How to view mysqladmin options and usage

To determine more options and usage of the myslqadmin command use the help command as shown below. It will display a list of available options.
  1. # mysqladmin --help  
We have tried our best to include almost all of the ‘mysqladmin‘ commands with their examples in this article, still, if we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.

Using the mysqldump Command

The mysqldump command creates a text version of the database. Specifically, it creates a list of SQL statements that can be used to restore/recreate the original database.

The syntax is:

$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] Your database username

[pass] The password for your database

[dbname] The name of your database

[backupfile.sql] The filename for your database backup

You can dump a table, a database, or all databases.

To dump all MySQL databases on the system, use the --all-databases shortcut:
  1. $ mysqldump -u root -p --all-databases > [backupfile.sql]  
Restoring a MySQL Database

Use this method to rebuild a database from scratch:
  1. $ mysql -u [username] -p [password] [database_to_restore] < [backupfile]  
Use this method to import into an existing database (in other words to restore a database that already exists).
  1. $ mysqlimport [options] database textfile1  
To restore your previously created custback.sql dump back to your 'Customers' MySQL database, you'd use:
  1. $ mysqlimport -u sadmin -p pass21 Customers custback.sql