Database Useful Tips
How to quickly secure MySQL after installation
$ /usr/bin/mysql_secure_installation
How to force MySQL server to use unicode
$ sudo -s
# echo "[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
" > /etc/mysql/conf.d/utf8.cnf
# /etc/init.d/mysql restart
# exit
How to limit access to MySQL to a sub-network
$ sudo -s
# iptables -A INPUT -p tcp -s 192.168.0.0/32 --dport 3306 -j ACCEPT
# iptables -A INPUT -p tcp --dport 3306 -j DROP
# exit
How to dump remote MySQL database to a local file
$ ssh <user@server> "mysqldump --user=<dbuser> --password=<dbpassword> \
<dbname> | bzip2 --stdout --force" | bzip2 --decompress --stdout \
> <dbname>_$(date +%Y-%m-%d_%H:%M:%S).sql
to dump data only:
$ ssh <user@server> "mysqldump --user=<dbuser> --password=<dbpassword> \
--compact --no-create-info --complete-insert \
<dbname> | bzip2 --stdout --force" | bzip2 --decompress --stdout \
> <dbname>_$(date +%Y-%m-%d_%H:%M:%S).sql
How to change MySQL server root password on Debian
$ sudo dpkg-reconfigure mysql-server
How to add a constraint to an existing SQLite table
CREATE UNIQUE INDEX table_i ON table(id, type);
How to disable ONLY_FULL_GROUP_BY mode in MySQL 5.7
$ sudo -s
# echo '[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
' > /etc/mysql/conf.d/fullgroup.cnf
# /etc/init.d/mysql restart
# exit
How to add a user in MySQL
GRANT ALL PRIVILEGES ON <database>.* TO '<user>'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;
How to convert DB to full Unicode in an older Rails project
Adjust encoding in database.yml as following:
encoding: utf8mb4
collation: utf8mb4_polish_ci
Create and run the following migration:
class ConvertToFullUnicode < ActiveRecord::Migration[5.0]
def change
config = Rails.configuration.database_configuration
db_name = config[Rails.env]["database"]
collate = 'utf8mb4_unicode_ci'
char_set = 'utf8mb4'
row_format = 'DYNAMIC'
execute("ALTER DATABASE #{db_name} CHARACTER SET #{char_set} COLLATE #{collate};")
ActiveRecord::Base.connection.tables.each do |table|
execute("ALTER TABLE #{table} ROW_FORMAT=#{row_format};")
execute("ALTER TABLE #{table} CONVERT TO CHARACTER SET #{char_set} COLLATE #{collate};")
end
end
end
(Based on this post.)