MySQL

Installation

Check here for latest version at - https://dev.mysql.com/downloads/repo/apt/

sudo -i
wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
dpkg -I mysql-apt-config_0.8.15-1_all.deb 
dpkg -i mysql-apt-config_0.8.15-1_all.deb
apt-get update
apt-get -s install mysql-server
apt-get -y install mysql-server
mysql_secure_installation
mysql -u root -p -e 'status;'

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

FIX ERROR: Cannot Install "mysql-server depends on mysql-server-5.7; however:

1. Back up your my.cnf file in /etc/mysql and remove or rename it

sudo mv -v /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

2. Remove the folder /etc/mysql/mysql.conf.d/ using

sudo rm -r /etc/mysql/mysql.conf.d/

3. Verify you don't have a my.cnf file stashed somewhere else (I did in my home dir!) or in /etc/alternatives/my.cnf use

sudo find / -name my.cnf

4. Backup and remove /etc/mysql/debian.cnf files (not sure if needed, but just in case)

sudo mv /etc/mysql/debian.cnf /etc/mysql/debian.cnf.bak
sudo apt purge mysql-server mysql-server-5.7 mysql-server-core-5.7
sudo apt install mysql-server

5. In case your syslog shows an error like "mysqld: Can't read dir of '/etc/mysql/conf.d/'" create a symbolic link:

sudo ln -s /etc/mysql/mysql.conf.d /etc/mysql/conf.d

https://askubuntu.com/questions/760724/16-04-upgrade-broke-mysql-server

Upgrade Ubuntu 14.04 from MySQL 5.5 to MySQL 5.6

sudo -i
dpkg -l | grep mysql
apt-get -s remove libmysqlclient18:amd64 mysql-client mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
apt-get -y remove libmysqlclient18:amd64 mysql-client mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
apt-get -s install mysql-client-5.6 mysql-client-core-5.6 mysql-common-5.6 mysql-server-5.6 mysql-server-core-5.6
apt-get -y install mysql-client-5.6 mysql-client-core-5.6 mysql-common-5.6 mysql-server-5.6 mysql-server-core-5.6
mysql -u root -p
reboot

Convert BAK MS SQL to NySQL

https://www.rebasedata.com/ubuntu-read-bak

HOWTO: FIX: Access denied for user 'debian-sys-maint'@'localhost'

Problem...

mysql_upgrade: Got error: 1045: Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) while connecting to the MySQL server

Fix...

sudo grep 'password' /etc/mysql/debian.cnf
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>';
mysql> exit;
sudo dpkg --configure -a

Thanks - https://stackoverflow.com/questions/11644300/access-denied-for-user-debian-sys-maint

Reduce Space Usage

You will find that the largest file in the MySQL directory is /var/lib/mysql/ibdata1 which is where the database stores all of its data.

427M    /var/lib/mysql/ibdata1

You can shrink this file and tell MySQL to use separate files to make things easier and reduce space usage.

Warning: these instructions will cause you to lose other databases if you have other databases on this mysql instance. Make sure that steps 1,2 and 6,7 are modified to cover all databases you wish to keep.

mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
DROP DATABASE [database_name];
sudo /etc/init.d/mysqld stop
sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile (and delete any other ib_logfile's that may be named ib_logfile0, ib_logfile1 etc...)
sudo /etc/init.d/mysqld start
create database [database_name];
mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Warning: these instructions will cause you to lose other databases if you have other databases on this mysql instance. Make sure that steps 1,2 and 6,7 are modified to cover all databases you wish to keep.

Reduce Memory Usage

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Basic...

[mysqld]
performance_schema      = off

For MySQL 8...

[mysqld]
performance_schema      = 0
show_compatibility_56   = 1 

Advanced...

[mysqld]
performance_schema      = off
key_buffer_size         = 16M
read_buffer_size        = 60K
sort_buffer_size        = 1M
innodb_buffer_pool_size = 64M
tmp_table_size          = 5M
thread_stack            = 192K
max_connections         = 10

For MySQL 8...

[mysqld]
performance_schema      = 0
show_compatibility_56   = 1
key_buffer_size         = 16M
read_buffer_size        = 60K
sort_buffer_size        = 1M
innodb_buffer_pool_size = 64M
tmp_table_size          = 5M
thread_stack            = 192K
max_connections         = 10

Disable IPv6 and X-Protocol

port                    = 3306
bind-address            = 127.0.0.1
mysqlx-port             = 33060
mysqlx-bind-address     = 127.0.0.1  
mysqlx                  = off

Differences between PRIMARY, UNIQUE, INDEX and FULLTEXT

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it's only used for a "full text search" feature.

Thanks - http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql#708508

HOWTO: Delete Binary Log Files

One issue which often people encountered is MySql binlog files.

These binlog files are very storage-intensive, most of the time for a small application each binlog file will be around 1.2GB.

-rw-r----- 1 mysql mysql 1.1G 2021-02-05 07:14 /var/lib/mysql/binlog.000021
-rw-r----- 1 mysql mysql 1.1G 2021-02-11 07:19 /var/lib/mysql/binlog.000022
-rw-r----- 1 mysql mysql  49M 2021-02-11 14:24 /var/lib/mysql/binlog.000023
-rw-r----- 1 mysql mysql 1.1G 2021-02-18 07:02 /var/lib/mysql/binlog.000024
-rw-r----- 1 mysql mysql 1.1G 2021-02-24 07:27 /var/lib/mysql/binlog.000025
-rw-r----- 1 mysql mysql 319M 2021-02-25 11:11 /var/lib/mysql/binlog.000026
-rw-r----- 1 mysql mysql 204K 2021-02-25 11:35 /var/lib/mysql/binlog.000027
-rw-r----- 1 mysql mysql 203M 2021-02-26 14:41 /var/lib/mysql/binlog.000028
-rw-r----- 1 mysql mysql  17K 2021-02-26 14:44 /var/lib/mysql/binlog.000029
-rw-r----- 1 mysql mysql  160 2021-02-26 14:45 /var/lib/mysql/binlog.index
-rw-r----- 1 mysql mysql 812M 2021-03-02 11:56 /var/lib/mysql/binlog.000030

You can stop the MySQL server and delete the files by hand, but the proper way is to use the PURGE command in the MySQL console...

mysql> PURGE BINARY LOGS BEFORE '2021-03-01';

or

mysql> RESET MASTER;

To disable binary logs completely...

sudo nano /etc/mysql/my.cnf

disable_log_bin # This line will disable the logs completely
# If you want to keep them for a certain amount of time, you can also just set a max time or size limit
# expire_logs_days        = 3
# max_binlog_size         = 500M

https://simonauer.medium.com/disabling-mysql-binary-logging-c43ce9aeac2e

or

sudo nano /etc/mysql/conf.d/disable_binary_log.cnf

[mysqld]
skip-log-bin

https://dba.stackexchange.com/questions/72770/disable-mysql-binary-logging-with-log-bin-variable

HOWTO: Include CREATE DATABASE Line In MysqlDump

mysqldump --user myuser --password --add-drop-database --databases mydatabase >mydatabase.sql

HOWTO: Copy 1 Column To Another

UPDATE `table` SET `column1` = `column2`

HOWTO: Import SQL File

mysql -u root -p databasename < mysqldumpfile.sql

HOWTO: Copy Table

CREATE TABLE student2 SELECT * FROM student

HOWTO: Secure MySQL

sudo mysql_secure_installation

HOWTO: Duplicate Databases Across Servers

If you need to keep the source database live, a work around I've used is this:

rsync /var/lib/mysql, while mysql is running, to a location preferably on the same partition so it's fast. Don't worry that the files will be inconsistent.

Open two consoles: bash and a mysql console

In the mysql console issue 'LOCK TABLES;'

In the bash console rsync /var/lib/mysql again. This second time will only be the changes so it will be fast, probably only a few seconds.

The moment it finishes issue 'UNLOCK TABLES;' in the mysql console.

At your leisure scp, netcat, tar or whatever the rsync'd copy of /var/lib/mysql to the other server.

HOWTO: Delete All Users Except Root

mysql> use mysql;
mysql> select * from user;
mysql> delete from user where User != 'root';
mysql> select * from user;
mysql> exit;

HOWTO: Delete A Database

mysql> DROP DATABASE dbname;

HOWTO: Select Delete Data From Older Than Today

Show data from today...

mysql> SELECT * FROM myTable WHERE DATE(myDateColumn) = DATE(NOW());

Delete data older than today...

mysql> DELETE FROM myTable WHERE DATE(myDateColumn) < DATE(NOW());

HOWTO: Analyze Database Tables

sudo mysqlcheck --analyze -u username -p dbname
sudo mysqlcheck --check -u username -p dbname

HOWTO: Repair Database Tables

sudo mysqlcheck --repair -u username -p dbname

HOWTO: Optimize Database Tables

sudo mysqlcheck --optimize -u username -p dbname

HOWTO: Check Engines

mysql> SHOW ENGINES;

HOWTO: Show Database Table Engine

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbname';

HOWTO: Alter The Character Set To UTF-8

mysql> ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

HOWTO: Export Multiple Tables

mysqldump -u user -p database table_1 table_2 table_3 >/path/to/filename.sql

HOWTO: Export Tables With Name Matching Pattern

root@serv: ~$ mysqldump YOURDBNAME $(mysql -D YOURDBNAME -Bse "SHOW TABLES LIKE 'table_pattern_%'") > /tmp/database-dump.sql

HOWTO: Change Root User Password

https://www.cyberciti.biz/faq/mysql-change-root-password/

HOWTO: Change User Password

mysql -u root -p
use mysql;
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('myPasswOrd');
exit;

HOWTO: Mysqldump Without Password In Cron

sudo -i
nano ~/.my.cnf

[mysqldump]
username = root
password = MyPasswOrd

mysqldump -uroot --databases mydatabase > /path/to/mydatabase.sql

Thanks - http://www.techiecorner.com/1619/how-to-setup-mysqldump-without-password-in-cronjob/

HOWTO: Mysqldump With Compression

mysqldump -u user_name -p database_name | bzip2 >database_name-2014061001.sql.bz2

HOWTO: Grant A User With GRANT Priviledges

mysql> GRANT ALL ON table.* TO 'root'@'192.168.0.13' WITH GRANT OPTION;

Truncate All Tables In A Database

mysql -uroot -pPaSSwOrd -Nse 'show tables' database_name | while read table; do mysql -uroot -pPaSSwOrd -e "truncate table $table" database_name; done

Search and Replace

mysql> UPDATE table SET field_name = REPLACE (field_name,'Item to replace here','Replacement text here');

Change Root User Password

https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

NEWER

sudo -i
systemctl stop mysql.service
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySuperPassw00rd!';" >/root/mysql_pw_change.txt
mysqld --user=root --init-file=/root/mysql_pw_change.txt &
systemctl restart mysql.service

NEW

sudo /etc/init.d/mysql stop
sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld/
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set authentication_string=PASSWORD("NEW_PASSWORD_GOES_HERE") where User='root';
flush privileges;
quit;
kill `pidof mysqld`
ls -lah /var/run/mysqld/
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

OLD

sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('MyPassWord') WHERE User='root'; FLUSH PRIVILEGES; exit;

Create A Database

mysql> CREATE DATABASE dbname;

Create A User

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER vimbadmin@'%' IDENTIFIED BY 'mypassword'; FLUSH PRIVILEGES;

Allow DB Access

mysql> GRANT ALL ON dbname.* TO 'username'@'localhost';

Setting Permissions

mysql> GRANT index, create, select, insert, update, delete, alter, lock tables ON mediawiki.* TO 'mediawiki'@'localhost' IDENTIFIED BY 'mediawiki';
mysql> GRANT index, create, select, insert, update, delete, alter, lock tables ON mediawiki.* TO 'mediawiki'@'%' IDENTIFIED BY 'mediawiki';

Importing A CVS File

mysqlimport [options] db_name textfile1 [textfile2 ...]

mysqlimport --user=user --pass=passW0rd --delete --ignore-lines=1 --fields-terminated-by=',' --lines-terminated-by='\r\n' database_name "/path/to/TableName.csv"

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Duplicating A Table

mysql> CREATE TABLE duplicate SELECT * FROM live;

mysql> CREATE TABLE duplicate SELECT * FROM live WHERE field1='value' LIMIT 0, 10000;