ip: 18.97.9.175
mysqldump -u username --password=Password DatabaseName > MyDatabase.sql
mysql -u MyUsername --password=MyPassword MyDatabase < MyDatabase.sql
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;
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
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//
set profiling=1; show profiles;
execute problematic query
show profiles; show profile for query 1; set profiling=0;
mysqlcheck -o --all-databases
OPTIMIZE TABLE 'myTable'
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;