mysqldump - a database backup program
The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server).
If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores. See mysqlhotcopy(1).
There are three general ways to invoke mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] –databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] –all-databases
If you do not name any tables following db_name or if you use the –databases or –all-databases option, entire databases are dumped.
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.
Set a new password using mysqladmin
shell> mysqladmin password “my new password”
Caution:
Do not use this command used if the server was started with the –skip-grant-tables option. No password change will be applied. This is true even if you precede the password command with flush-privileges on the same command line to re-enable the grant tables because the flush operation occurs after you connect. However, you can use mysqladmin flush-privileges to re-enable the grant table and then use a separate mysqladmin password command to change the password.
