阮胜昌的技术记录站-LinuxMySQL数据库运维

摘要：最近项目中某个模块稳定复现MySQL死锁问题，本文记录死锁的发生原因以及解决办法。测试环境：[root@node232 ~]# cat /etc/redhat-releaseCentOS release 6.9 (Final)[root@node232 ~]# uname -aLinux node232 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux[root@node232 ~]# mysql -e "\s"--------------mysql  V

摘要：Configuring Group Replication in Multi-Primary Mode1.接上面单主模式的配置，将4台实例全部停止[root@node232 group_Replication]#mysqld_multi  --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf stop 1-4或者：[root@node232 group_Replication]# mysqladmin -uroot -p -h127.0.0.1 -P3311 shutdown[root@node232 group_Replication]# mysqlad

摘要：Configuring Group Replication in Single-Primary Mode1.查看停止当前的mysql服务：#service mysqld stop2.查看grouprep.cnf启动配置文件：[mysqld1]# Server configurationdatadir=/var/lib/mysql1port=3311socket=/var/lib/mysql1/mysql.sock# General Replication configurationserver-id=1user=mysqlbinlog-format=ROWbinlog-checksum=NONElog-bin=mysql1-binrelay-log=my

摘要：通过配置多实现来做来实验的平台，在一台服务器上，配置4个MySQL实例1.将现在的MySQL服务器停止[root@node232 ~]# service mysqld stopShutting down MySQL.. SUCCESS!2.查看多实例repl.cnf文件 [root@node232 replication]# cat repl.cnf[mysqld1]datadir=/var/lib/mysql1port=3311socket=/var/lib/mysql1/mysql.sockserver-id=1user=mysqllog-bin=mysql1-binrelay-log=m

摘要：1.查询employees库的titles表：root@localhost[(none)]>use employees;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@localhost[employees]>select emp_no,title from titles where title='Manager' AND to_date>NOW();+--------+---------+| emp_no | ti

摘要：1.打个两个终端T1和T2T1:[root@node232 ~]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.18-log MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other

摘要：查看服务器是否开启SSL:root@localhost[(none)]>show variables like 'have_ssl'    -> ;+---------------+----------+| Variable_name | Value    |+---------------+----------+| have_ssl      | DISABLED |        #表示没有开启SSL+---------------+----------+1 row in set (0.00 sec)通过mysql_ssl_rsa_s

摘要：在一般开发或DBA看来，MySQL InnoDB存储引擎的锁粒度一定是行锁，然后思想就根深蒂固了。今天要跟大家分享行锁与表锁的条件创建表并插入数据：root@localhost[test]>create table test (id int primary key,name varchar(20) not null default '0');Query OK, 0 rows affected (0.02 sec)root@localhost[test]>insert into test (id,name) values (1,'rsc'),(2,'gmy');Qu

摘要：配置mysql多实例一、配置单独一个实例：1.创建目录：#mkdir /mysql/data/ -p#chown mysql:mysql /mysql2.初始化数据目录：[root@node232 ~]# mysqld --no-defaults --initialize --user=mysql --datadir=/mysql/data2021-06-20T00:37:05.282207Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server optio

摘要：原理图：基于GTID的流程图：GTID 相比binlog复制的优势如果传统复制一主两从的情况下.如果了两个从库数据并不是同步的.那么在主库挂了以后重新指向最新数据的从库为主库的时候就会出现数据不一致的情况.因为之前两个数据库中的数据是从master中同步的.而重新指向的新的从库为主库的情况下数据较全的从库并不会将数据同步到新的2号从库中.就会导致数据差异.需要人工干预来找到bi