How to check privileges for an mysql account.
To check the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR ‘root’@'localhost’;
+—————————————————-+
| Grants for root@localhost
+—————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*C406D12D7025EFA560629ABD992F09C9E28002C6′ WITH GRANT OPTION |
+—————————————————-+
1 row in set (0.00 sec)
mysql>
How to enable access from all machines for mysql user.
To create a user who has access from all machines in a given domain (for example, serverbuddies.com), you can use the “%” wildcard character in the host part of the account name:
mysql> CREATE USER ‘myname’@'%.serverbuddies.com’ IDENTIFIED BY ‘mypass’;
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user (Host,User,Password,…)
-> VALUES(’%.serverbuddies.com’,'myname’,PASSWORD(’mypass’),…);
mysql> FLUSH PRIVILEGES;
How to re-enable the grant table with mysql
root@dell:~# mysqladmin flush-privileges -uroot -p
When you create accounts with INSERT, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server. With CREATE USER, FLUSH PRIVILEGES is unnecessary.
As usual when you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.
Checking mysql extended-status
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more.
root@dell:~# mysqladmin status -uroot -p
Enter password:
Uptime: 197 Threads: 1 Questions: 135 Slow queries: 0 Opens: 40 Flush tables: 1 Open tables: 34 Queries per second avg: 0.685
For Extended Status
root@dell:~# mysqladmin extended-status -uroot -p
Setting up the Tunnel
The most common methods of setting up a tunnel are through putty or through command-line SSH.
To open an SSH tunnel via the command line, run:
ssh -L3307:127.0.0.1:3306 -p 22 -N -t -x user@myhost.com
Connecting with MySQL
You can connect to the MySQL server!
You want to choose 3307 for your local port so that it does not conflict with your existing local copy of MySQL.
mysql -u username -ppassword -h 127.0.0.1 -P 3307
