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;

Create database/table example

drop database if exists edapt_quiz;
create database edapt_quiz;
use edapt_quiz;

drop table if exists quizzes;
create table quizzes (
	id		        int unsigned not null auto_increment,
	name            varchar(64) not null,
    primary key (id)
) engine=innodb;
insert into quizzes (name) values ('Prodromal Questionnaire – Brief Version');

drop table if exists questions;
create table questions (
	id		        int unsigned not null auto_increment,
	quiz_id	        int unsigned not null,
	sort_order      smallint,
	text			text,
    primary key (id),
    foreign key (quiz_id) references quizzes(id)
) engine=innodb;