sudo apt install mysql-server;
sudo /etc/init.d/mysql start;
# sudo service mysql start
# systemctl restart mysql
sudo mysql_secure_installation; # Makes mysql more secure
# Change root password to more secure.
# Remove anonymous users.
# Disable remote root login. Root should only connect via `localhost`.
# Remove test database and access to it.
# Reload privilege tables.
sudo apt remove --purge mysql*
sudo apt-get remove --purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo rm -rf /var/log/mysql /var/log/mysql.*
sudo apt purge mysql*
sudo apt autoremove
sudo apt autoclean
sudo apt remove dbconfig-mysql
sudo systemctl status mysql
sudo systemctl start mysql
sudo /etc/init.d/mysql status
sudo /etc/init.d/mysql start
# Log into MySQL as root, with password.
sudo mysql -u root -p
# Run global script
mysql -u user -p < db.sql
# Run database specific script
mysql -u user -p db_name < db.sql
# Output results
mysql -u user -p db_name < db.sql > /tmp/output.txt
Commands are not case sensitive, but table names are. All commands must end with ;
.
;
- Execute/End current command.
ENTER
- Starts a new line. ;
is expected.
-- Create user
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
-- Give access to certain areas. In this case, it's for everything as *.* stands for dbName.tableName i.e. all of them.
GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
-- Fix errors
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
-- Reload the privileges.
FLUSH PRIVILEGES;
-- List all users.
SELECT user FROM mysql.user;
-- Show current user.
SELECT CURRENT_USER();
-- Delete user.
DROP USER 'user'@'localhost';
Options: ALL PRIVILEGES, CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, GRANT OPTION (User can give permissions).
-- Give a specific permission, for a specific table.
GRANT permission ON dbName.tableName TO '<user>'@'localhost';
-- Remove a permission.
REVOKE permission ON dbName.tableName FROM '<user>'@'localhost';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'MyNewPass';
FLUSH PRIVILEGES;
SHOW DATABASES; -- List databases.
SELECT database(); -- Show current database.
USE dbName; -- Select a database.
CREATE DATABASE dbName; -- Create a database.
DROP DATABASE dbName; -- Delete a database.
SHOW TABLES; -- List all tables.
DESCRIBE tableName; -- Display columns and types.
-- Shows the query that creates the table.
SHOW CREATE TABLE tableName;
-- Create a table.
CREATE TABLE tableName (column1 DATATYPE, column2 DATATYPE);
-- Example
CREATE TABLE user (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Important
name VARCHAR(255),
pass VARCHAR(255)
);
-- Rename a table.
RENAME TABLE tableName1 TO tableName2;
-- Change a column's datatype.
ALTER TABLE tablename MODIFY columnname DATATYPE;
-- Add a column at end.
ALTER TABLE tableName ADD columnName DATATYPE;
-- Add a column at certain location.
ALTER TABLE tableName ADD columnName DATATYPE AFTER columnName;
-- Delete a column.
ALTER TABLE tableName DROP columnName;
-- Change a column name. Has to be backticks.
ALTER TABLE tableName CHANGE `oldcolname` `newcolname` datatype(length);
-- Reset AUTO_INCREMENT id. For this to work, the table must be empty.
ALTER TABLE table AUTO_INCREMENT = 1;
Be VERY careful with this one. ALWAYS select first, delete second.
DROP TABLE tableName;
They are used for data integrity i.e. they prevent entering values that don't exist in the linked table (gives an error).
A FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
-- Foreign key definition during table creation.
CREATE TABLE user (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
pass VARCHAR(255),
phoneId INT,
FOREIGN KEY (phoneId) REFERENCES phone(id)
);
-- Add foreign key.
ALTER TABLE table1 ADD FOREIGN KEY (idColumn) REFERENCES table2(idColumn);
-- Remove foreign key.
ALTER TABLE tableName DROP FOREIGN KEY FK_columnName;
-- List foreign keys.
SELECT
tableName,
COLUMNNAME,
CONSTRAINT_NAME,
REFERENCED_tableName,
REFERENCED_COLUMNNAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_tableName = 'my_table';
-- One-liner.
SELECT tableName, COLUMNNAME, CONSTRAINT_NAME, REFERENCED_tableName, REFERENCED_COLUMNNAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_tableName = 'my_table';
-- Get records.
SELECT * FROM tableName;
-- Add record.
INSERT INTO tableName (column1, column2) VALUES (value1, value2);
-- Example
INSERT INTO user (id, name, pass)
VALUES (NULL, 'john', 'abc123');
-- Modify record.
UPDATE tableName SET columnName = 'value' WHERE criteria;
-- Example
UPDATE user SET name = 'Mike' WHERE id = 1;
-- Delete record.
DELETE FROM tableName WHERE columnName = <value>;
Insert if id
doesn't exit. Else, update the data at said id
.
+----+--------------+
| id | columnName |
+----+--------------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+----+--------------+
INSERT INTO table (id, columnName)
VALUES
(1, 'qux'),
(null, 'hex')
ON DUPLICATE KEY UPDATE
columnName = values(columnName);
+----+--------------+
| id | columnName |
+----+--------------+
| 1 | qux | -- Value updated
| 2 | bar |
| 3 | baz |
| 4 | hex | -- Inserted row
+----+--------------+
-- Check the size of all the databases
SELECT
table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;
-- Check the size of all the tables in a database
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "DATABASE_NAME" -- DATABASE_NAME
ORDER BY (data_length + index_length) DESC;
- Add this line in
/etc/mysql/my.cnf
[mysqld]
default-time-zone = "+01:00"
- Restart the server
sudo service mysql restart
To do this, we use the mysqldump
command which creates a file with the SQL statements necessary to re-create the database.
With --all-databases
or --databases
, mysqldump writes CREATE DATABASE
and USE
statements prior to the dump output for each database, in order to insure the data goes to the right database.
# Dump just the data, without creating the database
mysqldump dbName > backup.sql
mysqldump --add-drop-table --databases dbName > backup.sql
# Simplest command.
mysqldump --databases dbName > backup.sql
# Multiple databases.
mysqldump --databases db1 db2 > backup.sql
# Everything.
mysqldump --all-databases > backup.sql
# Prompt for password.
mysqldump -p --databases dbName > backup.sql
Options
# Add DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-database
# Add DROP TABLE statement before each CREATE TABLE statement
--add-drop-table
# Only database structure, without contents.
--no-data
# backup database - backup-db-name-20200903-032043.sql
mysqldump -u root --password="db-pass" --databases db-name > /home/user/backups/"backup-db-name-$(date +%Y%m%d-%H%M%S).sql"
# Edit crontabs i.e Add a cron job that runs as root. Opens editor.
sudo crontab -e
# backup database every day at 23:59
59 23 * * * mysqldump -u root --password="db-pass" --databases db-name > /home/user/backups/"backup-db-name-$(date +%Y%m%d-%H%M%S).sql"
# backup ALL databases every day at 23:59
59 23 * * * mysqldump -u root --password="db-pass" --all-databases > /home/user/backups/"backup-all-$(date +%Y%m%d-%H%M%S).sql"
If the dump was created without using --databases
, then the database must be manually created before restoring.
Also, the database must be specified with mysql dbName < backup.sql
.
Otherwise, just use:
# Restore a database.
mysql < backup.sql
# Prompt for password.
mysql -p < backup.sql
# From a compressed file.
gzip -d < backup.sql.gz | mysql
# If the database already exists and we want to restore it.
mysql dbName < backup.sql
Copy data from one database into another.
mysqldump -u root -p'password' source_db | mysql -u root -p'password' target_db
Import from remote to local
remotepath="/remote/path/backups/"
localpath="/local/path/backups/"
filename="dbname-$(date +%Y%m%d-%H%M%S)-import"
echo "backup: $filename"
echo "creating backup at remote server..."
ssh [email protected] "mysqldump -u root --password="pass" --databases dbname > $remotepath$filename"\
\
&& echo "downloading backup from remotepath to local..." \
&& rsync -av [email protected]:$remotepath$filename $localpath \
\
&& echo "importing backup into local mysql..." \
&& mysql -u root --password="pass" < $localpath$filename
Export from local to remote
remotepath="/remote/path/backups/"
localpath="/local/path/backups/"
filename="dbname-$(date +%Y%m%d-%H%M%S)-export"
echo "backup: $filename"
echo "creating local backup..."
mysqldump -u root --password="" dbname > $localpath$filename \
\
&& echo "transfering backup from local to remotepath..." \
&& rsync -av -e 'ssh' $localpath$filename [email protected]:$remotepath \
\
&& echo "importing transfered backup into remote mysql..." \
&& ssh [email protected] "mysql -u root --password="pass" dbname < $remotepath$filename" \
- Create a localhost connection as root on port 3306.
- Create a model AND name it.
- Forward Engineer the model in the localhost connection.
- Find the created database and populate it.
Model
holds all the schemas. There can be many modes, and each can have many schemas.
Schema
is the overall design of the database which defines the tables, number of columns, foreign keys... This rarely changed, if at all...
EER Diagram
is a visual representation of a schema with boxes for tables and lines for table relations.
Database
is an instance of a schema. It's also where the data lives.
A database is created by forward engineering a schema. An existing database is expanded with the new schema objects, but it does not alter the existing ones.
To overwrite them, the tables need to be dropped first, which is an option during the forward engineering.
Meta-data
is data about the database i.e. where the schema is stored.
sudo apt install mysqltuner
It's a utility used to find out what could be done in order to optimize MySQL for the hardware and workload.
It needs a bit of data to work properly, so a period should pass before running it, as it looks for usage patterns.
To run it, just use mysqltuner
.