Categories: MySQL

Mysql Mariadb Set Update Password

Nov 30, 2019 1 min.

Using mysqladmin CLI If you installed the db with empty password then set the password sudo mysqladmin password "<<new-password>>" -- OR mysqladmin --user=root password "<<new-password>>" Change the existing password mysqladmin --user=root --password=<<old-password>> password "<<new-password>>" Using SET PASSWORD SET PASSWORD FOR '<<user>>'@'<<hostname|ip>>' = PASSWORD('<<new-password>>'); Using SQL Update Change the password by the SQL update to the table mysql.users (Execute this query as root) USE mysql; UPDATE user SET password=PASSWORD('<<new-password>>') WHERE User='<<user>>' AND Host = '<<hostname|ip>>'; FLUSH PRIVILEGES; Using ALTER ALTER USER '<<user>>'@'<<hostname|ip>>' IDENTIFIED BY '<<new-password>>>'; FLUSH PRIVILEGES; OR

MySQL MariaDB Export and Import

Dec 26, 2017 2 min.

Export the database Use the following command to export the database mysqldump --user=<<db-user>> --password=<<db-password \  --host=<<db-host>> --port=3306 \ --single-transaction --compress --routines \ --result-file=dump.sql \ --databases <<db-name>>; Note: Include –single-transaction –compress to export and import quickly. Check documentation for the use --rountines will include procedures/functions in the dump (triggers included by default) --result-file will be the output file (instead of this param, output redirection like mysqldump [options] > dump.sql can be used, however it may have charset issues in OS like windows if you have any blob columns then include –hex-blob Import the database Use the following command to import the database

MySQL MariaDB Create User

Dec 26, 2017 1 min.

Create a user similar to root To create user with all the privileges similar to root. CREATE USER 'superuser'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; Create user for Backup A separate user with minimal privileges for taking database dump. CREATE USER 'backup'@'localhost' IDENTIFIED BY 'mypassword'; GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost'; Note:

MySQL MariaDB utf8 to utf8mb4

Dec 25, 2017 1 min.

Change server settings Refer: https://yottabrain.org/database/mysql/mysql-mariadb-set-utf8mb4-as-default-charset/ on AWS RDS a parameter group needs to be created to apply above settings: refer: http://aprogrammers.blogspot.in/2014/12/utf8mb4-character-set-in-amazon-rds.html Change database Run this SQL for each database: ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; Change size of indexed columns Reduce the length of indexed columns with utf8mb4 from 256 to 190 because index with 4byte utf8mb4 have issues ALTER TABLE mydb.mytable MODIFY COLUMN mycolumn varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Change Tables Run this SQL for each table:

MySQL MariaDB Set utf8mb4 as default charset

Nov 22, 2017 2 min.

By default charset is set to to latin1 and utf8. This can be checked by executing the following command MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ To set the charset as utf8mb4 do the following steps: