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 SETPASSWORDFOR'<<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) USEmysql;UPDATEuserSETpassword=PASSWORD('<<new-password>>')WHEREUser='<<user>>'ANDHost='<<hostname|ip>>';FLUSHPRIVILEGES;Using ALTER ALTERUSER'<<user>>'@'<<hostname|ip>>'IDENTIFIEDBY'<<new-password>>>';FLUSHPRIVILEGES;OR ALTERUSER'<<user>>'@'<<hostname|ip>>'IDENTIFIEDBYPASSWORDPASSWORD('<<new-password>>>');FLUSHPRIVILEGES;The above option will be useful when creating script in which password can be generated using SELECT PASSWORD('<<new-password>>) and use it in query like ALTER USER '<<user>>'@'<<hostname|ip>>' IDENTIFIED BY PASSWORD '<<hash-password>>>')

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. CREATEUSER'superuser'@'%'IDENTIFIEDBY'password';GRANTALLPRIVILEGESON*.*TO'superuser'@'%'WITHGRANTOPTION;FLUSHPRIVILEGES;Create user for Backup A separate user with minimal privileges for taking database dump. CREATEUSER'backup'@'localhost'IDENTIFIEDBY'mypassword';GRANTSELECT,SHOWVIEW,RELOAD,REPLICATIONCLIENT,EVENT,TRIGGER,LOCKTABLESON*.*TO'backup'@'localhost';Note: LOCK TABLES privilege can be removed if db has only InnoDB Create role and assign required privileges -- Create the role CREATEROLEtestrole;-- Grant privileges to the role -- OPTION 1: Grant all privileges on the database GRANTALLPRIVILEGESONtestdb.*TOtestrole;-- OPTION 2: Give selected privileges GRANTindex,create,select,insert,update,delete,drop,alter,locktablesONtestdb.

MySQL MariaDB utf8 to utf8mb4

Dec 25, 2017 1 min.

Change server settings Refer: on AWS RDS a parameter group needs to be created to apply above settings: refer: Change database Run this SQL for each database: ALTERDATABASEmydbCHARACTERSET=utf8mb4COLLATE=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 ALTERTABLEmydb.mytableMODIFYCOLUMNmycolumnvarchar(190)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;Change Tables Run this SQL for each table: ALTERTABLEmydb.mytableCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;Change Columns Run this SQL for the columns which needs to be stored as utf8mb4:

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: