MySql-Help-List.md

Get Version

mysql --version

Install

sudo apt-get install mysql-server mysql-client libmysqlclient-dev

Delete

sudo apt remove --purge mysql-server mysql-client mysql-common
sudo apt autoremove
sudo rm -rf /var/lib/mysql*

Display configuration file

my_print_defaults mysqld server mysqld-10.0 mariadb mariadb-10.0 client-server

OR

mysqld --print-defaults

Check if the MySQL process is running

ps aux | grep mysql

Login to console by 'root' user

mysql -u root -p

List databases

show databases;

To use database

use database_name;

Look at table

SELECT * FROM table_name;

Current database list tables

show tables;

Create dump

mysqldump -u user_name -p database_name > dump_name.sql

Copy dump from VPS to local machine

scp root@vps_ip_address:/vps_path/to/dump_name.sql /local_machine_path/to/dir

OR (BY SSH KEY)

scp -i ~/.ssh/key user_name@vps_ip_address:/vps_path/to/dump_name.sql /local_machine_path/to/dir

Load dump into database

mysql -u root -p database_name < /path/to/dump_name.sql 

Set/Change/Reset the MySQL root password

1. Stop the MySQL Server:

   sudo /etc/init.d/mysql stop

2. Start the mysqld configuration:

   sudo mysqld --skip-grant-tables &

3. Login to MySQL as root:

   mysql -u root mysql

4. Replace YOURNEWPASSWORD with your new password:

   UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;

5. Link to article