Mysql

1、初始化

mysql_install_db --datadir=/mydata_s1

2、启动

/usr/bin/mysqld_safe --defaults-file=my.cnf

3、登录

1）获取初始密码

cat /var/log/mysqld.log |grep password

2）登入数据库

mysql -S /mydata/mysql.sock  -uroot -p

3）ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

alter user 'root'@'localhost' identified by '123456';

4、排查mysql哪个sql占用cpu高

1）top 查父进程

2）top -Hp 父进程

3）查操作系统线程对应语句的信息

SELECT a.name,
       a.thread_id,
       a.thread_os_id,     
       a.processlist_id,   
       a.type,            
       b.user,           
       b.host,            
       b.db,           
       b.command,         
       b.time,           
       b.state,            
       b.info           
  FROM performance_schema.threads a
  LEFT JOIN information_schema.processlist b
    ON a.processlist_id = b.id
    WHERE a.`THREAD_OS_ID`=48223;

SELECT a.name,
       a.thread_id,
       a.thread_os_id,
       b.info           
  FROM performance_schema.threads a
  LEFT JOIN information_schema.processlist b
    ON a.processlist_id = b.id
    where a.`THREAD_OS_ID`=47497;

5、限制IP访问

mysql> REVOKE ALL ON *.* FROM compdev@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO compdev@'192.168.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO compdev@'192.167.%' IDENTIFIED BY '123456&';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO compdev@'192.166.%' IDENTIFIED BY '123456&';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

update user set grant_priv='Y' where user='compdev';


赋权：
GRANT ALL PRIVILEGES ON *.* TO compdev@'%' IDENTIFIED BY '123456';
收回权限：
REVOKE ALL ON *.* FROM compdev@'%';

6、查看binlog

mysqlbinlog -v --start-datetime='2021-01-21 00:09:00' --stop-datetime='2021-01-21 00:12:00' mysql-bin.061774 |grep 202101210010560612186614108111

7、热备，逻辑备份

mysqldump -uroot -p -S /mydata_s1/mysql.sock  --no-create-info  --skip-add-drop-table --set-gtid-purged=OFF -e --max_allowed_packet=167772160 --net_buffer_length=16384 esblogdb tlog_req_20191212 --where="NAME = 'gaozq' " >> /app/mysqllog/192.168.1.2-log.sql

8、导入数据

mysql -uroot -p -S /dashboardData1/temptask/mydata/mysql.sock esblogdb < /dashboardData1/temptask/resources/mysqllogdata/test.sql

9、主从断开恢复

跳过事务，恢复主从，但可能存在事务不一致的情况，如主从数据不同
stop slave;
set GTID_NEXT=
begin;commit;
set GTID_NEXT='AUTOMATIC';
start slave;

10、删除分区表某个分区（适用于单分区较大的情况）

思路：建ibd存储文件的硬链接，mysql执行drop操作，最后rm实体文件
sudo ln /mydata_s1/db/20180225.ibd  /mydata_s1/db/20180225.ibd.hdlk
drop table 20180225
rm /mydata_s1/db/20180225.ibd.hdlk

最后

以上就是曾经铃铛为你收集整理的Mysql日常运维Mysql的全部内容，希望文章能够帮你解决Mysql日常运维Mysql所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。