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 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: on AWS RDS a parameter group needs to be created to apply above settings: refer: 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:

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 | 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 | 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: SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(<<seconds>>, 'SECOND') FROM dual; e.g. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(1480340561, 'SECOND') FROM dual; Milliseconds to Date: Just divide the millseconds my 1000 so it will become seconds and apply the same query as above. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(<<milliseconds>>/1000, 'SECOND') FROM dual; e.g. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(1480340490930/1000, 'SECOND') FROM dual;

Oracle: Statistics Update

Mar 30, 2016 1 min.

Get Last statistics Update SELECT table_name, last_analyzed, sample_size, num_rows sample_pct FROM dba_tables WHERE owner = 'your_schema_name_here' ORDER BY last_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) execute DBMS_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) EXEC DBMS_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: Get list of connected users

Mar 30, 2016 1 min.

Following query returns the list of users who are currently connected to the Oracle database SELECT username, osuser, terminal, utl_inaddr.get_host_address(terminal) ip_address FROM system.v$session WHERE username is not null ORDER BY username, osuser;

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. SELECT object_type, object_name FROM user_objects WHERE STATUS != 'VALID' ORDER BY object_type; or SELECT owner, object_type, object_name FROM dba_objects WHERE STATUS != 'VALID' ORDER BY owner, object_type; Compile Invalid Objects Following SQL block can be used for compiling invalid objects DECLARE sql_statement varchar2(500); BEGIN dbms_output.put_line(chr(0)); dbms_output.put_line('Re-compilation of Invalid Objects'); dbms_output.put_line('---------------------------------'); dbms_output.put_line(chr(0)); FOR invalid in (SELECT object_type, object_name FROM user_objects o WHERE o.