Section: Databases

MySQL MariaDB Export and Import

Dec 26, 2017 1 min.

Export the database Use the following command to export the database mysqldump --user=root --password=mypassword \ --host=myhost1 --port=3306 \ --single-transaction --compress --routines \ --result-file=dump.sql \ --databases mydb; 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.

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: http://www.techiewiki.org/wiki/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:

Install Mariadb in Aws Ec2

Nov 22, 2017 1 min.

Setup 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. Verify Installation Verify correct version installed by executing mysql --version Login as root to check the installation mysql -u root -p Enable Network access To access the database from outside localhost comment the bind-address in in /etc/mysql/my.

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.

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.