FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
mysql lock all tables
Leave a reply
Tag Archives: MySQL
mysql 8 reset root password ubuntu
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
sed extract single table from mysqldump
grep -n "Table structure" database.sql sed -n '[starting_line_number],[ending_line_number] p' database.sql.sql > database_one_table.sql
ERROR 2061 (HY000): Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
mysql> ALTER USER 'some_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'some_password';
mysql grant all except mysql
SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT LIKE 'mysql';
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
[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
E: Unable to locate package percona-xtrabackup-80
To enable Percona tools:
percona-release enable tools release
* Enabling the Percona Tools repository
<*> All done
apt update
apt install percona-xtrabackup-80
tcpdump mysql traffic
tcpdump -i any port 3306
mysql extract table from dump
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` (
mysql kill select longer than
pt-kill --kill-busy-commands=Query --run-time 1m --interval 1 --each-busy-time 30s --print --match-info "(select|SELECT)" --group-by=fingerprint
mysql find error log
tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
mysql replication skip errors
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
try fix MySQL slave
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Incorrect date value: ‘0000-00-00’ for column ” at row 1
SET GLOBAL sql_mode = '';
set expire_logs_days
mysql> SET GLOBAL expire_logs_days=7;