MySQL

From Indie IT Wiki
Jump to: navigation, search

Reduce Memory Usage

How to reduce 400Mb to 40Mb!

sudo nano /etc/mysql/my.cnf

[mysqld]
performance_schema = off

Thanks - https://mariadb.com/resources/blog/starting-mysql-low-memory-virtual-machines

and...

performance_schema = off
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

# settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

Thanks - https://opensourcehacker.com/2011/03/31/reducing-mysql-memory-usage-on-ubuntu-debian-linux/

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: Copy 1 Column To Another

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

HOWTO: Import SQL File

mysql -u user -p < 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: 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

NEWEST

https://www.fossgeek.org/server-configuration/reset-mysql-root-password/

NEWER

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

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 -uroot
use mysql;
update user set authentication_string=PASSWORD("NEW_PASSWORD_GOES_HERE") where User='root';
flush privileges;
quit;
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;

Creating A Database

mysql> CREATE DATABASE mediawiki;

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;