MySQL reference for admin

Most things are more convenient to do through MySQL Workbench. But in order to initially create and configure the database, as well as backup it, a terminal is required. Here are the most essential MySQL terminal commands, without the hassle.

To log in to MySQL from a localhost, make a shortcut to the desktop with the following path:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" "-uroot" "-p"

Then after clicking on this shortcut, you will have only to enter password 🙂

If MySQL does not start: check services.msc and then  netstat -na | find "3306"

Now commands:

To use MySQL Workbench, you need to create a user in MySQL specifically for a specific IP address. Moreover, a separate user record will be created for each IP user, even if he has the same login / password. Those. if you want to use Workbench to connect to a remote database from a new IP address, you need to add a new user. For example:

CREATE USER 'login'@'' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.* To 'login'@'' IDENTIFIED BY 'password';


Lets now see what we have:

select host, user, password from mysql.user;


How to make backup:

mysqldump -uroot -ppassword database_name > C:\dump.sql

Restore backup in MySQL terminal (on local machine):
-uroot -p database_name < localhost.sql

Other useful commands:

Create a database on the sql server create database [databasename];
List all databases on the sql server show databases;
Switch to a database use [db name];
To see all the tables in the db show tables;
To see database’s field formats describe [table name];
To delete a db drop database [database name];
To delete a table drop table [table name];
Show all data in a table SELECT * FROM [table name];
Change a users password (from MySQL prompt) SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
Update database permissions/privilages FLUSH PRIVILEGES;
Dump all databases for backup [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Dump one database for backup [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
Restore database from backup [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

This entry was posted in MySQL (en). Bookmark the permalink.

Leave a Reply

🇬🇧 Attention! Comments with URLs are not allowed.
🇷🇺 Комментарии со ссылками удаляются автоматически.