grep -n "Table structure" database.sql sed -n '[starting_line_number],[ending_line_number] p' database.sql.sql > database_one_table.sql
sed extract single table from mysqldump
Leave a reply
Tag Archives: MySQL
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;
mysql uptime days
SELECT
VARIABLE_VALUE AS Uptime_seconds,
NOW() AS "Now",
NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",
DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Uptime';
mysql change root auth_socket
UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('changeme') WHERE User = 'root';
FLUSH PRIVILEGES