Mysql

Ubuntu setup

Have a mysql root password ready to go.

apt install mysql-server mysql-client libmysqlclient-dev
apt install python3-mysqldb  # if using python to access mysql
mysql_secure_installation

ERROR in UBUNTU 22.04 solved here: https://www.nixcraft.com/t/mysql-failed-error-set-password-has-no-significance-for-user-root-localhost-as-the-authentication-method-used-doesnt-store-authentication-data-in-the-mysql-server-please-consider-using-alter-user/4233

Log in as root to mysql database

mysql -u root -p mysql
select * from user;
delete from user where host = 'myserver';  # not needed if secure installation completed above
create user barney@localhost identified by 'xxxxxxxx';
create database bookstore;
grant all privileges on bookstore.* to barney@localhost;  # regular account
grant select on bookstore.* to barney@localhost;          # read-only account
grant lock tables on bookstore.* to barney@localhost;     # need lock tables to run mysqldump
grant file on *.* to barney@localhost;                    # write output to files
flush privileges;

Show Permissions

Logged in as root:

show grants for barney@localhost;

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 can only write to the /var/lib/mysql-files/ directory):

select ... into outfile "/var/lib/mysql-files/goodbooks.txt";

To load a file into a table:

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

Save into a CSV file:

select * from users into outfile '/tmp/users.csv' fields terminated by ',' 
    optionally enclosed by '"' lines terminated by '\n';

Backing up and restoring a database

To back up the database "bookstore" to a file sq5.sql:

export MYSQL_PWD=XXXXX
mysqldump --no-tablespaces --opt 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/conf.d/mysql.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;

Troubleshooting

When attempting to load a database from a mysqldump backup, you get

ERROR 1227 (42000) at line 81: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

You need to remove all DEFINER lines from the mysqldump file:

sed 's/DEFINER=[^*]*\*/\*/g' backup.sql > new_backup.sql