CREATE USER 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' IDENTIFIED BY 'THIS_USER_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Calling a mysql query from linux or dos command line
There are times when you want to call a mysql command from the bash or dos script then call this script in a scheduler (cron job).
The example below will show you how to do so.
mysql -uYOURUSERNAME -pYOURPASSWORD -DYOURDATABASE -e"CALL YOURSTOREDPROCEDURE('YOUR_SP_PARAMETER')"
Explanation:
-u is where you put your username, you can either put a space or no space at all after the -u (both works). As you can see I prefer not to put any space.
-p is where you put your password.
-D is your database name. That’s a capital D.
-e this is where your query will go. It needs to be quoted (“). If it’s calling a stored procedure or stored function, you’ll need to use the CALL keyword, otherwise if it’s just a simple query don use the CALL keyword.
MySQL Backup and Restore – Using Command Line
Here is how you backup database using command line. The first line is to backup and the second line is to restore.
These commands work on both Windows and Linux. In Windows you might want to setup mysql bin directory the environment PATH, you can do this easily by pressing Window + Break > Advanced System Settings > Environment > PATH. Then run the cmd or the command prompt. If you already open a command prompt before you setup the PATH, you need to restart your command prompt
MySql doesn’t export stored procedure/function by default. It also doesn’t export triggers. If you need those, the you’ll need to add “–routines –triggers” to your command. Even if you don’t use stored procedure, function, or triggers it is safer if you issue your backup with those option enabled.
$ mysqldump -uusername -ppassword --routines --triggers --lock-tables=false database_name > YOURSQLFILE.sql
$ mysql -uusername -ppassword database_name < YOURSQLFILE.sql
If you need to backup all database. The user that we usually use to backup all database is "root". This will backup all triggers, and functions. You will need to pre-create other users that has certain privilege over stored procedure or functions.
mysqldump -uusername -ppassword --opt --events --all-databases > alldb.sql
mysql -uusername -ppassword < alldb.sql