Eureka Moment database:mysql

database:mysql:backup

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Bash script MySql Backup If you host any MySql databases, you should certianly make use of the command line tools to make backups. There are never enough backups. The script below this text, in combination with the specified crontab entries, will give you the following backup:

database:mysql:connections

Anonymous (anonymous@undisclosed.example.com) — 2021-10-12T12:50:30+00:00

Change connection settings in MySql SET GLOBAL max_connect_errors = 100000000; SET GLOBAL max_connections = 10000; SHOW VARIABLES LIKE "max_connections"; SHOW VARIABLES LIKE "max_connect_errors"; FLUSH HOSTS;

database:mysql:create_db

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Create mysql database CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

database:mysql:duplicate_table_and_data

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Duplicate table and data in MySql Use the following SQL code to duplicate table structure: CREATE TABLE newtable LIKE oldtable; And if you need the same data in the new table (for testing?), copy the data with this SQL code: INSERT INTO newtable SELECT * FROM oldtable;

database:mysql:duplicates_and_gaps

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Find gaps or duplicates in number sequences in MySql select l.id + 1 as start from sequence as l left outer join sequence as r on l.id + 1 = r.id where r.id is null select min(l.id + 1) as start from sequence as l left outer join sequence as r on l.id + 1 = r.id where r.id is null

database:mysql:get_item_position_in_resultset

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Get row position in ordered result set (pagination) SET @val := 0; CREATE TEMPORARY TABLE IF NOT EXISTS inv_items_tmp AS (SELECT (@val := @val + 1) AS val, id FROM inv_items ORDER BY inv_no,model); SET @oneless := (SELECT val FROM inv_items_tmp WHERE id=2977); SELECT COUNT(*) FROM inv_items_tmp WHERE val<@oneless; DROP TABLE inv_items_tmp;

database:mysql:insert_if_not_exists

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

MySQL Insert If Not Exists There are 3 possible solutions to insert data into MySql table (when it is possible that data might be duplicate): using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE. Method 1: using REPLACE REPLACE INTO `table` SET `field1` = 'value1', `field2` = 12345, `field3` = 12678;

database:mysql:manage_users

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Manage MySql users CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; -- Grant all privileges including the grant option (basically an additional root user) GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'%' WITH GRANT OPTION; -- Grant all privileges GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'%'; -- Grant read-only privileges GRANT SELECT ON *.* TO 'newuser'@'%';

database:mysql:move_data_folder

Anonymous (anonymous@undisclosed.example.com) — 2020-02-21T10:34:00+00:00

Relocate MySql data folder rsync -avzh /var/lib/mysql /data mv /var/lib/mysql /var/lib/mysql.bak mkdir /var/lib/mysql mount -B /data/mysql /var/lib/mysql Additionally /data/mysql /var/lib/mysql auto defaults,nofail,nobootwait,bind 0 2

database:mysql:password

Anonymous (anonymous@undisclosed.example.com) — 2025-06-26T13:38:06+00:00

MySql password and old_password /* Old password type */ SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass'); /* New password type */ SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('mypass'); ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

database:mysql:restore_deleted_root

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Restoring Accidently Deleted Root User in MySQL If you, like me, have accidentally deleted your root user in MySQL, you will need to add the user manually. First off, start MySQL with –skip-grant-tables by editing /etc/my.cnf [mysqld] skip_grant_tables

database:mysql:start

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

MySql * Bash script MySql Backup * Change connection settings in MySql * Count weekdays between two dates in MySql * Create mysql database * Duplicate table and data in MySql * Find gaps or duplicates in number sequences in MySql * Get row position in ordered result set (pagination) * Manage MySql users * MySQL Insert If Not Exists * MySql password and old_password * Refresh timestamp field on insert and update * Relocate MySql data folder * Restoring Accidently Deleted Root Use…

database:mysql:ts_on_ins_and_upd

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Refresh timestamp field on insert and update When working with databases, it's a good practice to do as much data manipulation inside the database server, instead of usercode. So, using triggers, stored procedures and functions is an excellent way to modify linked data across tables (or calculate certain values).

database:mysql:weekdays

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Count weekdays between two dates in MySql DELIMITER $$ CREATE DEFINER=`su`@`%` FUNCTION `TOTAL_WEEKDAYS`(date1 DATE, date2 DATE) RETURNS INT(11) RETURN ABS(DATEDIFF(date2, date1)) + 1 - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY), ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2 - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1) - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7)$$ DELIMITER ;