MySQL 5 administration: Difference between revisions

From Elvanör's Technical Wiki
Jump to navigation Jump to search
No edit summary
Line 31: Line 31:
and then using real_connect instead of just connect.
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.
* Do not put this option (local-infile=1) in your my.cnf file (in the [client] section)! If you do, the mysql client will start fine, but mysqldump won't be able to start! This is maybe a bug; or maybe this option should be set on a different section than [client].


== Configuration ==
== Configuration ==


* [[Configuring MySQL 5 general options]]
* [[Configuring MySQL 5 general options]]

Revision as of 11:16, 21 March 2007

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.

  • Do not put this option (local-infile=1) in your my.cnf file (in the [client] section)! If you do, the mysql client will start fine, but mysqldump won't be able to start! This is maybe a bug; or maybe this option should be set on a different section than [client].

Configuration