MySQL 5 administration
Some random information about MySQL administration.
Installation
When you finished installation and compilation, on UNIX you must run some setup scripts. In particular, mysql_install_db. This script must be ran as the mysql user, else if you run it as root you will get failures later. Eg do something as:
sudo -u mysql mysql_install_db
Backup & Restore
The backup can be done as:
mysqldump -u root -p --all-databases > backup.sql
The restore as:
mysql -u root -p < backup.sql
Warning: if you do that, the MySQL user table will change and you will need to restart the server to see the changes (although of course, there is probably a better way than simply restarting).
Using LOAD DATA INFILE
If you don't compile MySQL with the --enable-local-infile option (note: this seems to be the case by default, unless for the binaries provided from MySQL directly), then if you want to use this command you have to explicitly request it at connection time.
- When using the command-line client, add --local-infile=1 to your arguments.
- When using PHP, this can be set with
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, "1");
and then using real_connect instead of just connect.
- I think you may also somehow put this option in the my.cnf file, but I am not sure.