su - mysql -s /bin/bash
mysqld_safe --upgrade=MINIMAL --skip-grant-tables --skip-networking
mysql> DROP DATABASE sys;
ERROR 3679 (HY000): Schema directory './sys/' does not exist
mkdir /var/lib/mysql/sys
chown mysql:mysql /var/lib/mysql/sys
mysql> drop database sys;
Query OK, 101 rows affected (0.03 sec)
Percona Monitoring and Management (PMM) is an open-source database monitoring, management, and observability solution for MySQL, PostgreSQL, and MongoDB.
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt my_db > ~/my_db_triggers.sql
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
systemctl stop mysql.service
systemctl set-environment MYSQLD_OPTS=”–skip-networking –skip-grant-tables”
systemctl start mysql.service
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘the-new-password’;
flush privileges;
systemctl unset-environment MYSQLD_OPTS
systemctl revert mysql.service
systemctl restart mysql.service
grep -n "Table structure" database.sql sed -n '[starting_line_number],[ending_line_number] p' database.sql.sql > database_one_table.sql
mysql> ALTER USER 'some_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'some_password';
SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT LIKE 'mysql';
[Repl] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='repl', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='binlog.000008', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[Repl] Slave I/O thread for channel '': connected to master '[email protected]:3306',replication started in log 'binlog.000008' at position 157
To enable Percona tools:
percona-release enable tools release
* Enabling the Percona Tools repository
<*> All done
apt update
apt install percona-xtrabackup-80
tcpdump -i any port 3306
sed -n -e '/DROP TABLE.*`my_table`/,/UNLOCK TABLES/p' mydump.sql > table.sql
make sure table is correct:
egrep -h "DROP TABLE|CREATE TABLE" table.sql
DROP TABLE IF EXISTS `my_table`;
CREATE TABLE `my_table` (
pt-kill --kill-busy-commands=Query --run-time 1m --interval 1 --each-busy-time 30s --print --match-info "(select|SELECT)" --group-by=fingerprint
tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
auto skip
until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do
mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";
sleep 1;
done