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'@'11.111.111.111' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* To 'login'@'11.111.111.111' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'login'@'11.11.111.111' WITH GRANT OPTION;
Lets now see what we have:
select host, user, password from mysql.user;
Don’t forget FLUSH PRIVILEGES;
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 |