I had done installation of mysql from the below link and find useful:
https://oracle-base.com/articles/mysql/mysql-installation-on-linux
Some Useful MySQL Command:-
How to connect to MySQL database
# mysql –u root –p
Or with sudo privileges
#sudo mysql –u root
How to set MySQL Root password?
If you have fresh installation of MySQL server, then it doesn’t require any password to connect it as root
user. To set MySQL password for 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 MySQL root password, then you need to type the following
command. For example, say your old password is 123456 and you want to change it with new password
say xyz123.
#mysqladmin -u root -p123456 password 'xyz123'
How to check MySQL Server is running?
To find out whether MySQL server is up and running, use the following command.
# mysqladmin -u root -p ping
How to Check which MySQL version I am running?
The following command shows MySQL version along with the current running status.
# mysqladmin -u root -p version
How to Find out current Status of MySQL server?
To find out current status of MySQL server, use the following command. The mysqladmin command
shows the status of uptime with running threads and queries.
# mysqladmin -u root -p status
How to check status of all MySQL Server Variable’s and value’s?
To check all the running status of MySQL server variables and values, type the following command. The
output would be similar to below.
# mysqladmin -u root -p extended-status
Or
# mysqladmin -u root -p variables
How to check all the running Process of MySQL server?
The following command will display all the running process of MySQL database queries.
# mysqladmin -u root -p processlist
How to create a Database in MySQL server?
To create a new database in MySQL server, use the command as shown below.
# mysqladmin -u root -p create databasename
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 press ‘y‘.
# 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 shutdown MySQL server Safely?
To shutdown MySQL server safely, type the following command.
#mysqladmin -u root -p shutdown
You can also use the following commands to start/stop MySQL server.
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
Some useful MySQL Flush commands
Following are some useful flush commands with their description.
flush-hosts: Flush all host information from 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 Sleeping MySQL Client Process?
Use the following command to identify sleeping MySQL client process.
# mysqladmin -u root -p processlist
# mysqladmin -u root -p kill 5 (ID)
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 run multiple mysqladmin commands together?
If you would like to execute multiple ‘mysqladmin commands together, then the command would be
like this.
# mysqladmin -u root -p processlist status version
How to Connect remote mysql server
To connect remote MySQL server, use the -h (host) with IP Address of remote machine.
# 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 remote MySQL server, and 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?
To start/stop MySQL replication on salve server, use the following commands.
# mysqladmin -u root -p start-slave
# 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 event scheduler.
# mysqladmin -u root -p debug
How to view mysqladmin options and usage
To find out more options and usage of myslqadmin command use the help command as shown below. It
will display a list of available options.
# mysqladmin --help
