Introduction
If you have a fresh MySQL server installation, 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.
# 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?
Use the following command to determine whether the MySQL server is up and running.
# mysqladmin -u root -p ping
Enter password
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.
# 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 its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and 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 table: 8 Queries per second avg: 0.059
How to determine the current Status of the MySQL server?
To determine the current status of the MySQL server, use the following command. The mysqladmin command shows the uptime status with the running threads and queries.
# 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 the one below.
# 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 MySQL server's running variables and values, use the following command.
# 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 Processes of the MySQL server?
The following command will display all the running processes of MySQL database queries.
# 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 the MySQL server, use the below command.
# mysqladmin -u root -p create databasename
Enter password
# 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 its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and 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.
mysql> show databases;
| Database |
| information_schema |
| databasename |
| mysql |
| test |
8 rows in set (0.01 sec)
mysql>
How to drop a database in a MySQL server?
To drop a database in a 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 terrible thing to do.
Any data stored in the database will be destroyed.
Do you 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 shut down the MySQL server Safely?
To shut down the MySQL server safely, type the following command.
mysqladmin -u root -p shutdown
Enter password
You can also use the following commands to start/stop the MySQL server.
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
Some useful MySQL Flush commands
The following are some useful commands with their description.
- flush-hosts. Flush all host information from the host cache.
- flush-tables. Flush all tables.
- flush-threads. Flush all threads cache.
- flush-logs. Flush all information logs.
- flush-privileges. Reload the grant tables (same as reload).
- flush-status. Clear status variables.
# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status
How to kill the Sleeping MySQL Client Process?
Use the following command to identify a sleeping MySQL client process.
# 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 following the command with kill and process ID as shown below.
# 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, pass the process IDs separated by commas, as shown below.
# mysqladmin -u root -p kill 5,10
How to run multiple mysqladmin commands together?
If you would like to execute multiple ‘mysqladmin‘ commands together, the command would be like this.
# 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 its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and 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
How to connect to a remote MySQL server?
To connect to a remote MySQL server, use the -h (host) with the IP Address of the remote machine.
# mysqladmin -h 172.16.25.126 -u root -p
How to execute commands on a remote MySQL server?
Let’s say you would like to see the status of a remote MySQL server, then; the command would be:
# mysqladmin -h 172.16.25.126 -u root -p status
How to start/stop MySQL replication on a slave server?
Use the following commands to start/stop MySQL replication on the same server to store MySQL server Debug Information in 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.
# 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 below. It will display a list of available options.
# mysqladmin --help
We have tried our best to include almost all of the ‘mysqladmin‘ commands with their examples in this article; if we’ve missed anything, please 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.
Syntax
$ 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:
$ mysqldump -u root -p --all-databases > [backupfile.sql]
Restoring a MySQL Database
Use this method to rebuild a database from scratch:
$ mysql -u [username] -p [password] [database_to_restore] < [backupfile]
Use this method to import into an existing database (in other words, to restore an existing database).
$ mysqlimport [options] database textfile1
To restore your previously created custback.sql dump back to your 'Customers' MySQL database, you'd use:
$ mysqlimport -u sadmin -p pass21 Customers custback.sql
Summary
This article taught us how to write important facts about MySQL Server with code examples.