DKs blog - MySQL administration

Connection strings examples
Ffmpeg
Linux administration
MySQL administration
Programming
       Android examples
       C# examples
       CSS examples
       Elastic Search
       Excel macros
       HTML/javascript examples
       MySQL examples
       Python
       Regular Expressions
       SQL (T-SQL) examples
       VB.NET examples
sublime text
Windows administration
WWW - reklame

MySQL administration

Backup MySQL database - example

mysqldump -u username --password=Password DatabaseName > MyDatabase.sql

Restore MySQL database - example

mysql -u MyUsername --password=MyPassword MyDatabase < MyDatabase.sql

Add user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'mypassword';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION; 

GRANT ALL ON mydb.* TO 'username'@'localhost';

FLUSH PRIVILEGES;

Multi master replication

user for replication: slave
password for replication: slave
database for replication: gd

set node1 & node2 i /etc/hosts on both servers

192.168.1.10 node1
192.168.1.11 node2

server1 mysql:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
FLUSH PRIVILEGES;

/etc/mysql/my.cnf

[mysqld]

...
   replicate-same-server-id = 0
   auto-increment-increment = 2
   auto-increment-offset = 1 
   server-id = 1 
   master-host = node2 
   master-user = slave 
   master-password = slave 
   master-port = 3306 
   master-connect-retry = 60 
   replicate-do-db = gd 
   log-bin = /var/log/mysql/mysql-bin.log 
   relay-log = /var/lib/mysql/slave-relay.log 
   relay-log-index = /var/lib/mysql/slave-relay-log.index 
   expire_logs_days = 10 
   max_binlog_size = 500M

server2 mysql:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
FLUSH PRIVILEGES;

/etc/mysql/my.cnf

[mysqld]

...
   replicate-same-server-id = 0
   auto-increment-increment = 2
   auto-increment-offset = 2 
   server-id = 2 
   master-host = node1 
   master-user = slave 
   master-password = slave 
   master-port = 3306 
   master-connect-retry = 60 
   replicate-do-db = gd 
   log-bin = /var/log/mysql/mysql-bin.log 
   relay-log = /var/lib/mysql/slave-relay.log 
   relay-log-index = /var/lib/mysql/slave-relay-log.index 
   expire_logs_days = 10 
   max_binlog_size = 500M

commands:

show slave status;
show slave status \G;
show master status;
show master status \G;

stop slave;
reset slave;
start slave;

CHANGE MASTER TO MASTER_HOST='node1', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
CHANGE MASTER TO MASTER_HOST='node2', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
  
--
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

LOAD DATA FROM MASTER; --copy DB from master, BUT master is locked during that so use only if master is small othervise dump/recreate

How to create Store Procedure or Function in MySQL

important is DELIMITER // at the begining and // at the end

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `GetSomething`(`ID` INT)
	RETURNS varchar(1024)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(ParentID,-1) INTO ch FROM
        (SELECT ParentIDFROM Category WHERE ID = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END//

How to find why mysql query (SQL) is executing slowly

set profiling=1;
show profiles;

execute problematic query

show profiles;
show profile for query 1;
set profiling=0;

How to optimize all tables im mysql DB

mysqlcheck -o --all-databases

How to optimize table with SQL query

OPTIMIZE TABLE 'myTable'

How to delete old user and create new user in Mysql 8.0

DROP USER 'newUser'@'%';
CREATE USER 'newUser'@'%' IDENTIFIED WITH caching_sha2_password BY 'myPassword' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'newUser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

DK's Blog