To know more about Mysql, visit https://dev.mysql.com/
From the official docs -
MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query Language) to manage its data.
Installation of Mysql 5.7 on Ubuntu 18.04.3 LTS - ref
-
Common steps to be executed on both Master as well as Slave instance
- Update apt repository and install mysql-server
sudo apt update # On Ubuntu 18.04, only the latest version of MySQL is included in the APT package repository by default. At the time of writing, that’s MySQL 5.7 sudo apt install mysql-server -y # For fresh installations, run the included security script sudo mysql_secure_installation # Verify installation sudo systemctl stop mysql.service sudo systemctl start mysql.service sudo systemctl enable mysql.service sudo systemctl status mysql.service
- Enable root user's login by changing root user's auth plugin through mysql safe mode
# First stop existing mysql process sudo systemctl stop mysql.service # Start msyqld in safe mode and update the auth plugin for root from `auth_socket` to `mysql_native_password` sudo su mkdir -p /var/run/mysqld chown mysql:mysql /var/run/mysqld sudo mysqld_safe --skip-grant-tables & mysql -uroot
use mysql; SELECT user,authentication_string,plugin,host FROM mysql.user; UPDATE user set authentication_string=PASSWORD("root") WHERE user='root'; UPDATE user set plugin="mysql_native_password" WHERE user='root'; FLUSH PRIVILEGES; quit;
# Stop mysqld process and restart mysql service ps -ef | grep mysql sudo kill -9 <pid of mysqld_safe --skip-grant-tables> <pid of /usr/sbin/mysqld --basedir=/usr...> sudo systemctl start mysql # Login to mysql shell from localhost mysql -uroot -proot
- Change data directory location to separate volume
# Stop existing mysql server sudo systemctl stop mysql # Update path of new data dir sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf > datadir = /data/mysql # Update apparmour to use new data dir location sudo vim /etc/apparmor.d/usr.sbin.mysqld ------------------------------ -------------------------- |# Allow data dir access | |# Allow data dir access | | /var/lib/mysql/ r, | ---> | /data/mysql/ r, | | /var/lib/mysql/** rwk, | | /data/mysql/** rwk, | ------------------------------ --------------------------- # Copy content of existing data directory sudo cp -r /var/lib/mysql/* /data/mysql/. sudo chown -R mysql:mysql /data/mysql # Restart processes sudo systemctl restart apparmor sudo systemctl restart mysql
- Update
/etc/mysql/mysql.conf.d/mysqld.cnfto enable following# Connection settings bind-address = 0.0.0.0 # Bin log settings server-id = 1 # If server-id = 1 for master instance, then it should be 2 for slave instance log_bin = mysql-bin.log log_bin_index = mysql-bin.log.index relay_log = mysql-relay-bin relay_log_index = mysql-relay-bin.index # GTID settings gtid_mode = ON enforce_gtid_consistency = ON
- Allow remote login for root user:
# Login to mysql shell from server's localhost mysql -uroot -prootuse mysql; GRANT ALL ON *.* TO 'root'@'%' identified by 'root'; FLUSH PRIVILEGES;
- Apply and verify all changes
# First restart mysql server sudo systemctl restart mysql# Check variables SHOW VARIABLES like '%log_bin%'; SHOW VARIABLES like '%gtid%';
- Update apt repository and install mysql-server
-
Steps to be executed on
Master- Login to mysql shell and create a replication user and grant replication permission
CREATE USER 'repl_user'@'<replica-instance-ip>' IDENTIFIED BY 'replica_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'<replica-instance-ip>'; FLUSH PRIVILEGES;
- Stop writes to master database
FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
- Check the master binlog position. This will be the starting point of replica
SHOW MASTER STATUS\G # Sample output *************************** 1. row *************************** File: mysql-bin.000327 Position: 1568347 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 98a2e938-5e0b-11ea-ad82-506b8d83ce00:1-102151 1 row in set (0.00 sec)
- Create master db dump
mysqldump -h <master-instance-ip> -u root -p --all-databases > master_dump.sql
- Enable write on master
SET GLOBAL read_only = OFF; UNLOCK TABLES;
- Login to mysql shell and create a replication user and grant replication permission
-
Steps to be executed on
Slave- Restore master dump in slave
# Restore in replica mysql -uroot -p < master_dump.sql
- Stop slave threads
STOP SLAVE;
- Point slave to master's checkpoint details
CHANGE MASTER TO MASTER_HOST='<master-instance-ip>', MASTER_USER='repl_user', MASTER_PASSWORD='replica_password', MASTER_LOG_FILE='mysql-bin.000327', # This is the file name shown in SHOW MASTER STATUS\G MASTER_LOG_POS=1568347; # This is the position shown in SHOW MASTER STATUS\G
- Allow slave to replicate from master by resuming slave threads
START SLAVE;
- Check replication status
SHOW SLAVE STATUS\G # Sample output *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.11.16.30 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000327 Read_Master_Log_Pos: 1878387 Relay_Log_File: mysql-relay-bin.000018 Relay_Log_Pos: 1878600 Relay_Master_Log_File: mysql-bin.000327 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1878387 Relay_Log_Space: 1878894 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 98a2e938-5e0b-11ea-ad82-506b8d83ce00 Master_Info_File: /data/mysql-data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 98a2e938-5e0b-11ea-ad82-506b8d83ce00:98628-102350 Executed_Gtid_Set: 98a2e938-5e0b-11ea-ad82-506b8d83ce00:1-98579:98628-102350, b52bf103-87c0-11ea-97b7-506b8d103aa4:1-8 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
- Restore master dump in slave
-
Verify master-slave replication by changing something in master and reading it from replica.
-
Update
/etc/mysql/conf.d/mysql.cnfto make slaveread-only[mysqld] read_only NOTE: Even after this config - 2 type of users can still write to slave 1. Users with `Super_priv` priviliges 2. User used by slave threads (`repl_user` as per this guide)
-
Restart slave instance to apply read-only changes
sudo systemctl restart mysql
-
Uninstall mysql:
sudo apt -y purge mysql* sudo apt -y autoremove sudo rm -rf /etc/mysql sudo rm -rf /var/lib/mysql*
-
Sample custom
mysqld.cnf:#------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here # Connection settings bind-address = 0.0.0.0 # Directory settings datadir = /data/mysql-data # Bin log settings server-id = 1 log_bin = mysql-bin.log log_bin_index = mysql-bin.log.index relay_log = mysql-relay-bin relay_log_index = mysql-relay-bin.index # GTID settings gtid_mode = ON enforce_gtid_consistency = ON
-
User Management - [AWS]
- User create/view/update/delete
# Create user and allow login from localhost CREATE USER 'username'@'localhost' IDENTIFIED BY 'Abc123'; CREATE USER 'username'@'10.%' IDENTIFIED BY 'Abc123'; # View user info SELECT user, host, authentication_string FROM mysql.user WHERE user='root'; # Update username RENAME USER 'username'@'localhost' TO 'newusername'@'localhost'; # Delete user DROP USER 'username'@'localhost';
- Password update/revoke
# Update password for any user SET PASSWORD FOR 'jim'@'localhost' = PASSWORD('NewPass'); # Update your own password SET PASSWORD = PASSWORD('NewPass'); # Expire password for any user ALTER USER 'jim'@'localhost' PASSWORD EXPIRE; # List accounts without password SELECT host, user FROM mysql.user WHERE authentication_string = ''; # Identify users with duplicate passwords SELECT authentication_string, group_concat(user) FROM mysql.user GROUP BY authentication_string HAVING count(user)>1;
- Permission show/grant/revoke
# View grants for user SHOW GRANTS FOR 'username'; SHOW GRANTS FOR 'username'@'localhost'; # Grant all permissions GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; # Grant specific permissions on specific database.table GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'username'@'%' WITH GRANT OPTION; E.g. GRANT SELECT ON database_name.* TO 'username'@'localhost'; GRANT SELECT ON database_name.table_name TO 'username'@'localhost'; # Revoke all permissions REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost'; # Revoke specific permissions on specific database.table REVOKE ALL SELECT ON database_name.* FROM 'username'@'localhost'; NOTE: Revoking a grant only possible if a grant was given earlier. # E.g. Grants for debezium user GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debezium'@'10.%';
- User create/view/update/delete
-
Dump and restore
# --- 1. Table --- # Dump 1 table mysqldump -h <hostname> --port=3306 -u <username> -p <db-name> <table-name> > table.sql # Restore 1 table mysql -uroot -proot db-name -f < table.sql # --- 2. Database --- # Dump 1 db mysqldump -h <hostname> --port=3306 -u <username> -p <db-name> > db.sql # Restore 1 db mysql -uroot -proot db-name -f < db.sql # Dump all dbs mysqldump -u <username> -p --all-databases > dbs.sql # Restore all dbs mysql -uroot -p < dbs.sql # --- 3. Grants --- # Install package sudo apt install percona-toolkit # Dump grants pt-show-grants --host <hostname> --user 'username' --password 'password' --ignore root@'%','mysql.session'@'localhost',rdsadmin@localhost,'rdsrepladmin'@'%' > grants.sql # Restore grants mysql -uroot -proot mysql < grants.sql
-
Database and table size
SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "database_name" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
-
Tables without primary key
select tab.table_schema as database_name, tab.table_name from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tco.constraint_type is null and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') and tab.table_type = 'BASE TABLE' order by tab.table_schema, tab.table_name;
-
Log all statements
SET global log_output = 'FILE'; SET global general_log_file='/Applications/MAMP/logs/mysql_general.log'; SET global general_log = 1;