MySQL
Uit DeVliegendeWiki
Backup
De gebruikelijke manier om een MySQL-database te archiveren, is middels het commando mysqldump:
mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
Als je dit bv. via Cron doet, icm. met FTP over SSH, kun je gemakkelijk backups maken en elders opslaan.
Bron: http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
Commando's
Here are some common tasks and how to complete them.
| Task | Command(s) | Comments |
|---|---|---|
| Log in to MySQL Client on local machine | mysql -u root -p | This line has to be entered at a Command Shell. It is assumed that you have credentials to log in as root. If you need to log in to a remote MySQL client, you might need to enter: mysql -u root -h over.the.rainbow.nu -p. If you absolutely want to, you can enter the password (that's what the -p switch is for) as part of the command. It than becomes: mysql -u root -ppasword, without space between -p and the actual password.
I guess you can log in to a MySQL Client with any account that has been instantiated for a database that this client has access to. |
| Show available databases | show databases; |
This is after you are logged into a client. |
| Open a database | use databasename; | This is after having logged into the MySQL Client. I guess you log into the database with the credentials with which you logged into the MySQL Client. |
| Show all user accounts | select User from mysql.user | mysql.user is a MySQL system database that contains user accounts, amongst others. I guess you can only execute this command if you have something like global CREATE USER privilege or the DELETE privilege, like by logging in as root. |
| Remove a user | drop user xyz; | Again, you need to have global CREATE USER privilege or DELETE privilege. (http://dev.mysql.com/doc/refman/5.0/en/drop-user.html).
When this gives an error like ERROR 1396 (HY000): Operation DROP USER failed for 'xyz'@'%' , you might want to try something like drop user xyz@localhost. |
| Create user for a specific database | grant all on mydb.* to 'someuser'@'somehost' identified by 'xyz';
Een practischer voorbeeld: grant all on testbase.* to 'testuser'@'localhost' identified by 'xyz'; | When a database is funerable to attacks, it might be a good idea to not grant more rights than needed. That's why it might be smart to create an account with only rights to a specific database.
MySQL commands seem case-insensitive. The example above was given with commands in upper case (http://dev.mysql.com/doc/refman/5.0/en/adding-users.html), but lower case worked fine. The apostrophes around the account identifier and the password, are compulsory. When the client only approaches the server from the same machine (e.g., a web interface running on the same machine as the server, somehost can be substituted with localhost. E.g.: GRANT ALL ON mydb.* TO 'someuser@'localhost'; |
| Remove database | drop database xyz; | |
| Create database | create database xyz; | |
| Show version of MySQL | show version(); |
Connection testing
Op http://www.ardamis.com/2008/05/26/a-php-script-for-testing-a-mysql-database-connection/ staat een fantastisch script voor testen. Ik kan het niet goed hieronder plakken omdat het dezelfde escape-codes gebruikt als MediaWiki voor letterlijke weergave van tekst.
