Section: Databases

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: 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: 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:

Install Mariadb in Aws Ec2

Nov 22, 2017 2 min.

These steps can be used install mariadb on any Ubuntu based Linux system (local desktop, aws ec2, etc.) Install MariaDB Execute the following commands: curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash sudo apt install mariadb-server During this setup, it will ask for root password. Enter and keep a note of it. Install a specific version curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.3" sudo apt install mariadb-server For NON LTS version Goto this link: https://downloads.

Oracle Milliseconds to Date

Nov 28, 2016 1 min.

Seconds to Date: SELECTto_timestamp('1970-01-01','yyyy-mm-dd')+numtodsinterval(<<seconds>>,'SECOND')FROMdual;e.g. SELECTto_timestamp('1970-01-01','yyyy-mm-dd')+numtodsinterval(1480340561,'SECOND')FROMdual;Milliseconds to Date: Just divide the millseconds my 1000 so it will become seconds and apply the same query as above. SELECTto_timestamp('1970-01-01','yyyy-mm-dd')+numtodsinterval(<<milliseconds>>/1000,'SECOND')FROMdual;e.g.

Oracle: Statistics Update

Mar 30, 2016 1 min.

Get Last statistics Update SELECTtable_name,last_analyzed,sample_size,num_rowssample_pctFROMdba_tablesWHEREowner='your_schema_name_here'ORDERBYlast_analyzed;Note: last_analyzed column shows the last analyzed date sample_size and sample_pct should be same (or difference should be very less) Statistics Update (for schema) executeDBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'your_schema_name_here',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS',DEGREE=>8,CASCADE=>TRUE);Statistics Update (for table) EXECDBMS_STATS.GATHER_TABLE_STATS('your_schema_name_here','your_table_name_here',estimate_percent=>'',method_opt=>'for all indexed columns size auto',DEGREE=>8,CASCADE=>TRUE);

Oracle Invalid Objects

Jan 20, 2016 1 min.

Get List of Invalid Objects Following query can be used to get the list of Invalid objects. SELECTobject_type,object_nameFROMuser_objectsWHERESTATUS!='VALID'ORDERBYobject_type;or SELECTowner,object_type,object_nameFROMdba_objectsWHERESTATUS!='VALID'ORDERBYowner,object_type;Compile Invalid Objects Following SQL block can be used for compiling invalid objects DECLAREsql_statementvarchar2(500);BEGINdbms_output.put_line(chr(0));dbms_output.put_line('Re-compilation of Invalid Objects');dbms_output.put_line('---------------------------------');dbms_output.put_line(chr(0));FORinvalidin(SELECTobject_type,object_nameFROMuser_objectsoWHEREo.status='INVALID'ANDo.object_typeIN('PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALIZED VIEW','PROCEDURE')ORDERBYo.object_type)LOOPIFinvalid.object_type='PACKAGE BODY'THENsql_statement:='alter package '||invalid.object_name||' compile body';ELSEsql_statement:='alter '||invalid.object_type||' '||invalid.object_name||' compile';ENDIF;BEGINEXECUTEIMMEDIATEsql_statement;dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||invalid.object_name,32)||' : compiled');EXCEPTIONWHENOTHERSTHENdbms_output.put_line(sqlerrm);END;ENDLOOP;END;/or SETheadingoff;SETfeedbackoff;SETechooff;SETlines999;SPOOLrun_invalid.sqlSELECT'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'FROMdba_objectsWHERESTATUS='INVALID'ANDobject_typeIN('PACKAGE','FUNCTION','PROCEDURE');SPOOLoff;SETheadingon;SETfeedbackon;SETechoon;@run_invalid.sqlor EXECUTL_RECOMP.recomp_serial('schema name');