Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
If Last_IO_Errno: 0, so its fine if error like 2049 it could be password related issues
grant select on database_name.* to 'user'@'%' identified by 'password';
log-bin=mysql-bin
server-id=1
innobackupex --user=root --password=password /path/to/backupdir
innobackupex --user=root --password=password --apply-log /path/to/backupdir/$TIMESTAMP/
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'remote_ip' IDENTIFIED BY 'slave_password';
server-id=2
cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 100
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=100;
START SLAVE;
mysql> SHOW SLAVE STATUS \G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 13
Adding another slave, run on slave:
innobackupex --user=root --password=password --slave-info /path/to/backupdir
innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/
On master:
GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'new_slave_ip' IDENTIFIED BY 'new_slave_pass';
cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 100
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl2', MASTER_PASSWORD='new_slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=100;
START SLAVE;
skip-slave-start
server-id=3
Use sshfs or rsync to move data
/etc/init.d/mysql does not start mysqld at system start up on Ubuntu
grep debian-sys-maint /etc/mysql/debian.cnf
ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
mysql> UPDATE mysql.user SET Password = password(‘password’) WHERE User = ‘debian-sys-maint’;
service mysql restart
SHOW VARIABLES LIKE ‘log_bin’;
Workaround is tu use proxysql
mysql_query_rules:
{
rule_id=1
active=1
match_pattern="\@\@query_cache_size"
replace_pattern="null"
apply=0
},
{
rule_id=2
active=1
match_pattern="\@\@query_cache_type"
replace_pattern="null"
apply=0
},
{
rule_id=3
active=1
match_pattern="\@\@tx_isolation"
replace_pattern="null"
apply=0
},
mysqladmin variables | grep socket
[mysqld]
default_authentication_plugin=mysql_native_password
mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.01 sec)
mysql_secure_installation
Securing the MySQL server deployment.
yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm -y
yum repolist enabled | grep “mysql.*-community.*”
mysql-connectors-community/x86_64 MySQL Connectors Community 165
mysql-tools-community/x86_64 MySQL Tools Community 115
mysql57-community/x86_64 MySQL 5.7 Community Server 444
systemctl start mysqld
grep ‘temporary password’ /var/log/mysqld.log
2020-08-08T18:56:50.367913Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxxxxx
apt-get install mysql-server
mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
Its possible your database files was restored on wrong server and wrong MySQL version, maybe too old MySQL server.
If you have issues to import a database using Virtualmin (Webmin) you need to disable or increase quotas:
Total server quota: Unlimited
Server administrator’s quota: Unlimited
By default it was set limit, so that error means: Disk quota exceeded
mysql> SELECT SQL_NO_CACHE * from some_tabe limit 10;