Mysql

Ubuntu setup

Root password should already be created.

Log in as root to mysql database

mysql -u root -p mysql
select * from user;
delete from user where host = 'myserver';
create user barney@localhost identified by 'xxxxxxxx';
create database bookstore;
grant all privileges on bookstore.* to barney@localhost;
grant file on *.* to barney@localhost;
flush privileges;

File I/O

You should have already granted "file" privileges to user:

grant file on *.* to barney@localhost;
flush privileges;

To execute SQL commands in a text file (from the command line):

mysql -u barney -p bookstore < mycommands.sql

To execute SQL commands in a text file (from the mysql prompt):

mysql> use bookstore;
mysql> source /home/barney/sql/mycommands.sql;

To read data out to a file (NOTE: The mysql user must have write permissions on the intended output directory):

select ... into outfile "/home/barney/sql/goodbooks.txt";

To load a file into a table:

load data local infile "/home/barney/sql/goodbooks.txt" into table books;

Backing up and restoring a database

To back up the database "bookstore" to a file sq5.sql: mysqldump --opt -pXXXXX bookstore > bookstore.sql

To restore a database from backup (overwrites any existing): mysql -u root -p bookstore < bookstore.sql

Searching on three-character words

• Edit /etc/mysql/my.cnf to add this line to the [mysqld] section:

ft_min_word_len=3

• Restart mysql.
• For each table you are doing a fulltext search on, run this command:

repair table <table_name> quick;