• 2024年8月7日（mysql主从 ）

    回顾
    主服务器

    [root@master_mysql ~]# yum -y install rsync

    [root@master_mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar 

    [root@master_mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

    [root@master_mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
    [root@master_mysql ~]# cd /usr/local/mysql/
    [root@master_mysql mysql]# mkdir mysql-files
    [root@master_mysql mysql]# useradd -r -s /sbin/nologin mysql

    [root@master_mysql mysql]# id mysql
    uid=997(mysql) gid=995(mysql) 组=995(mysql)
    [root@master_mysql mysql]# chown mysql:mysql ./mysql-files/

    [root@master_mysql mysql]# rm -rf /etc/my.cnf
    [root@master_mysql mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

    [root@master_mysql mysql]# ./bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

    [root@master_mysql mysql]# cp support-files/mysql.server /etc/init.d/mysql8

    [root@master_mysql mysql]# vim my.cnf

    1. [mysqld]
    2. basedir=/usr/local/mysql
    3. datadir=/usr/local/mysql/data
    4. socket=/tmp/mysql.sock
    5. port=3306
    6. log-error=/usr/local/mysql/data/db01-master.err
    7. log-bin=/usr/local/mysql/data/binlog
    8. server-id=10
    9. character_set_server=utf8mb4

    [root@master_mysql mysql]# service mysql8 start
    . SUCCESS! 
    [root@master_mysql mysql]# ./bin/mysql -P 3306 -p

    mysql> alter user 'root'@'localhost' identified by '123456';
    mysql> exit

    [root@master_mysql mysql]# service mysql8 restart
    Shutting down MySQL. SUCCESS! 
    Starting MySQL.. SUCCESS! 

    从服务器（不用初始化也不用启动）

    [root@slave_mysql ~]# yum -y install rsync

    [root@slave_mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
    [root@slave_mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

    [root@slave_mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
    [root@slave_mysql ~]# useradd -r -s /sbin/nologin mysql
    [root@slave_mysql ~]# mkdir /usr/local/mysql/mysql-files
    [root@slave_mysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/

    [root@slave_mysql ~]# rm -rf /etc/my.cnf
    [root@slave_mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

    主服务器（同步）

    [root@master_mysql ~]# rm -rf /usr/local/mysql/data/auto.cnf 
    [root@master_mysql ~]# service mysql8 stop
    Shutting down MySQL. SUCCESS! 
    [root@master_mysql ~]# rm -rf /usr/local/mysql/data/auto.cnf 
    [root@master_mysql ~]# ls /usr/local/mysql/data/
    [root@master_mysql ~]# rsync -av /usr/local/mysql/data root@192.168.8.165:/usr/local/mysql/  #从服务器IP地址

    从服务器

    [root@slave_mysql ~]# vim /usr/local/mysql/data/my.cnf

    1. [mysqld]
    2. basedir=/usr/local/mysql
    3. datadir=/usr/local/mysql/data
    4. socket=/tmp/mysql.sock
    5. log-error=/usr/local/mysql/data/err.log
    6. log-relay=/usr/local/mysql/data/relaylog
    7. character_set_server=utf8mb4
    8. server-id=11

    [root@slave_mysql ~]# service mysql8 start

    [root@slave_mysql ~]# /usr/local/mysql/bin/mysql -p123456

    1、 主从复制的实现

     [root@master_mysql ~]# vim /etc/profile

    export PATH=/usr/local/mysql/bin:$PATH

    [root@master_mysql ~]# sorce /etc/profile

    1. mysql> create user 'slave'@'192.168.8.%' identified by 'slave_123'
    2. ';
    3. Query OK, 0 rows affected (0.01 sec)
    6. mysql> grant replication slave on *.* to 'slave'@'192.168.8.%';
    7. Query OK, 0 rows affected (0.01 sec)
    9. mysql> flush privileges;
    10. Query OK, 0 rows affected (0.00 sec)
    12. mysql> flush tables with read lock;
    13. Query OK, 0 rows affected (0.00 sec)
    14. mysql> show master status;

    [root@slave_mysql ~]# vim /etc/profile

    export PATH=/usr/local/mysql/bin:$PATH

    [root@slave_mysql ~]# source /etc/profile 

    [root@slave_mysql ~]# mysql -h192.168.8.164 -uslave -pslave_123 --get-server-public-key

    [root@slave_mysql ~]# mysql -p123456 -P3306

    1. mysql> stop slave;
    2. Query OK, 0 rows affected, 1 warning (0.00 sec)
    3.  
    4. mysql> reset slave;
    5. Query OK, 0 rows affected, 1 warning (0.01 sec)
    6.  
    7. mysql> change master to
    8.     -> master_host='192.168.8.164',
    9.     -> master_user='slave',
    10.     -> master_password='slave_123',
    11.     -> master_log_file='binlog.000009',
    12.     -> master_log_pos=447;
    13. Query OK, 0 rows affected, 8 warnings (0.00 sec)
    14.  
    15. mysql> start slave;
    16. Query OK, 0 rows affected, 1 warning (0.01 sec)
    17. mysql> show slave status\G;
    18.  
    19.
    2、主服务器创建表并向表里添加数据
    1. mysql> unlock tables;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> create database if not exists test charset utf8mb4;
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> use test;
    8. Database changed
    9. mysql> create table student(id int primary key,name varchar(45) not null,gender varchar(4) not null);
    10. Query OK, 0 rows affected (0.01 sec)
    11.  
    12. mysql> insert into student values(1,'张三','男');
    13. Query OK, 1 row affected (0.02 sec)
    14.  
    15. mysql> insert into student values(2,'凤凰','女');
    16. Query OK, 1 row affected (0.01 sec)
    17.  
    18. mysql> insert into student values(3,'张三','男');
    19. Query OK, 1 row affected (0.00 sec)
    20. mysql> select * from student;
    21. +----+--------+--------+
    22. | id | name   | gender |
    23. +----+--------+--------+
    24. |  1 | 张三   | 男     |
    25. |  2 | 凤凰   | 女     |
    26. |  3 | 张三   | 男     |
    27. +----+--------+--------+
    28. 3 rows in set (0.00 sec)
    3、从服务器查看同步并写入东西（从服务器不容许写入东西）
    1. mysql> show databases;
    2. +--------------------+
    3. | Database           |
    4. +--------------------+
    5. | information_schema |
    6. | mysql              |
    7. | performance_schema |
    8. | sys                |
    9. | test               |
    10. +--------------------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> use test;
    14. Reading table information for completion of table and column names
    15. You can turn off this feature to get a quicker startup with -A
    16.  
    17. Database changed
    18. mysql> select * from student;
    19. +----+--------+--------+
    20. | id | name   | gender |
    21. +----+--------+--------+
    22. |  1 | 张三   | 男     |
    23. |  2 | 凤凰   | 女     |
    24. |  3 | 张三   | 男     |
    25. +----+--------+--------+
    26. 3 rows in set (0.00 sec)
    27.  
    28. mysql> insert into student values(4,'李网','女');
    29. Query OK, 1 row affected (0.00 sec)
    30.  
    31. mysql> select * from student;
    32. +----+--------+--------+
    33. | id | name   | gender |
    34. +----+--------+--------+
    35. |  1 | 张三   | 男     |
    36. |  2 | 凤凰   | 女     |
    37. |  3 | 张三   | 男     |
    38. |  4 | 李网   | 女     |
    39. +----+--------+--------+
    40. 4 rows in set (0.00 sec)
    41.
    4、主服务器插入数据
    1. mysql> insert into student values(6,'章节','男');
    2. Query OK, 1 row affected (0.01 sec)
    3.  
    4. mysql> select * from student;
    5. +----+--------+--------+
    6. | id | name   | gender |
    7. +----+--------+--------+
    8. |  1 | 张三   | 男     |
    9. |  2 | 凤凰   | 女     |
    10. |  3 | 张三   | 男     |
    11. |  6 | 章节   | 男     |
    12. +----+--------+--------+
    13. 4 rows in set (0.00 sec)
    5、从服务器查看同步
    1. mysql> select * from student;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  2 | 凤凰   | 女     |
    7. |  3 | 张三   | 男     |
    8. |  4 | 李网   | 女     |
    9. |  6 | 章节   | 男     |
    10. +----+--------+--------+
    11. 5 rows in set (0.00 sec)
    二、SQL语句
    1、新增

    1. insert into库名称.表名

            （id，username，password）values（1,"abc","123"）

    2. insert into 表名称 values(1, "name","word")

    3. insert into 表名称 select* from 其他表

    4. insert into 表 values (),()

    2、删除

    1. delete from 表名

    2. deletefrom表名称 where id=3

    3. delete from 表名称 where age>8

    4. delete from 表 where name on （"a","b","c"）

    3、修改

     1. update mysql.user set host='%' where name='root'

     2. update user set password='abc' where username="zhangsan"

    4、查询
            1. 单表查询

    1.1 select 字段名列表 from表名称，索引

    1.2

    5、远程连接数据库的

    username

    password

    url （mysql IP或者域名 数据库名称 端口号 ）

    1. mysql> create user 'li'@'%' identified by '123';
    2. Query OK, 0 rows affected (0.02 sec)
    3.  
    4. mysql> grant all on *.* to 'li'@'%';
    5. Query OK, 0 rows affected (0.01 sec)
    6.  
    7. mysql> flush privileges;
    8. Query OK, 0 rows affected (0.01 sec)
    9.  
    10. mysql> select count(*) from student;
    11. +----------+
    12. | count(*) |
    13. +----------+
    14. |        4 |
    15. +----------+
    16. 1 row in set (0.06 sec)
    17.  
    18. mysql> select count(1) from student;
    19. +----------+
    20. | count(1) |
    21. +----------+
    22. |        4 |
    23. +----------+
    24. 1 row in set (0.06 sec)
    25.  
    26. mysql> select a.*,b.* from student as a,student as b;
    27. +----+--------+--------+----+--------+--------+
    28. | id | name   | gender | id | name   | gender |
    29. +----+--------+--------+----+--------+--------+
    30. |  6 | 章节   | 男     |  1 | 张三   | 男     |
    31. |  3 | 张三   | 男     |  1 | 张三   | 男     |
    32. |  2 | 凤凰   | 女     |  1 | 张三   | 男     |
    33. |  1 | 张三   | 男     |  1 | 张三   | 男     |
    34. |  6 | 章节   | 男     |  2 | 凤凰   | 女     |
    35. |  3 | 张三   | 男     |  2 | 凤凰   | 女     |
    36. |  2 | 凤凰   | 女     |  2 | 凤凰   | 女     |
    37. |  1 | 张三   | 男     |  2 | 凤凰   | 女     |
    38. |  6 | 章节   | 男     |  3 | 张三   | 男     |
    39. |  3 | 张三   | 男     |  3 | 张三   | 男     |
    40. |  2 | 凤凰   | 女     |  3 | 张三   | 男     |
    41. |  1 | 张三   | 男     |  3 | 张三   | 男     |
    42. |  6 | 章节   | 男     |  6 | 章节   | 男     |
    43. |  3 | 张三   | 男     |  6 | 章节   | 男     |
    44. |  2 | 凤凰   | 女     |  6 | 章节   | 男     |
    45. |  1 | 张三   | 男     |  6 | 章节   | 男     |
    46. +----+--------+--------+----+--------+--------+
    47.  
    48. #别名
    49. mysql> select id as 编号,name,gender from student;
    50. +--------+--------+--------+
    51. | 编号   | name   | gender |
    52. +--------+--------+--------+
    53. |      1 | 张三   | 男     |
    54. |      2 | 凤凰   | 女     |
    55. |      3 | 张三   | 男     |
    56. |      6 | 章节   | 男     |
    57. +--------+--------+--------+
    6、mysql函数

    排序：max min

    汇总：count sum avg

    数制：二进制 八进制 十进制 十六进制

    只有select子句和having子句，order by 子句中能使用聚合函数1，where子句红中不能使用聚合函数，当使用聚合函数查询以后，不能使用where条件，如果添加条件 ，就使用having

    1. mysql> select * from student order by gender desc;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  3 | 张三   | 男     |
    7. |  6 | 章节   | 男     |
    8. |  2 | 凤凰   | 女     |
    9. +----+--------+--------+
    10. 4 rows in set (0.00 sec)
    11.  
    12. mysql> select * from student order by gender asc;
    13. +----+--------+--------+
    14. | id | name   | gender |
    15. +----+--------+--------+
    16. |  2 | 凤凰   | 女     |
    17. |  1 | 张三   | 男     |
    18. |  3 | 张三   | 男     |
    19. |  6 | 章节   | 男     |
    20. +----+--------+--------+
    21. 4 rows in set (0.00 sec)
    22.  
    23. mysql> select gender as 性别,count(gender) as 人数 from student grroup by gender;
    24. +--------+--------+
    25. | 性别   | 人数   |
    26. +--------+--------+
    27. | 男     |      3 |
    28. | 女     |      1 |
    29. +--------+--------+
    30. 2 rows in set (0.00 sec)
    31.  
    32. mysql> create table product(
    33.     -> id int primary key auto_increment,
    34.     -> name varchar(45) not null,
    35.     -> price float not null,
    36.     -> qty int not null);
    37. Query OK, 0 rows affected (0.01 sec)
    38.  
    39. mysql> desc product;
    40. +-------+-------------+------+-----+---------+----------------+
    41. | Field | Type        | Null | Key | Default | Extra          |
    42. +-------+-------------+------+-----+---------+----------------+
    43. | id    | int         | NO   | PRI | NULL    | auto_increment |
    44. | name  | varchar(45) | NO   |     | NULL    |                |
    45. | price | float       | NO   |     | NULL    |                |
    46. | qty   | int         | NO   |     | NULL    |                |
    47. +-------+-------------+------+-----+---------+----------------+
    48. 4 rows in set (0.01 sec)
    49.  
    50. mysql> insert into product (name,price,qty) values("香蕉",8.5,200);
    51. Query OK, 1 row affected (0.00 sec)
    52.  
    53.  
    54. mysql> insert into product (name,price,qty) values("苹果",12.5,40)0);
    55. Query OK, 1 row affected (0.00 sec)
    56.  
    57. mysql> insert into product (name,price,qty) values("菠萝",12.4,700);
    58. Query OK, 1 row affected (0.01 sec)
    59.  
    60. mysql> insert into product (name,price,qty) values("哈密瓜",18.3,7400);
    61. Query OK, 1 row affected (0.00 sec)
    62.  
    63. mysql> select * from product order by price;
    64. +----+-----------+-------+-----+
    65. | id | name      | price | qty |
    66. +----+-----------+-------+-----+
    67. |  1 | 香蕉      |   8.5 | 200 |
    68. |  3 | 菠萝      |  12.4 |  70 |
    69. |  2 | 苹果      |  12.5 | 400 |
    70. |  4 | 哈密瓜    |  18.3 | 400 |
    71. +----+-----------+-------+-----+
    72.  
    73.  
    74. mysql> select * from (select * from product order by qty) as a ordder by a.price;
    75. +----+-----------+-------+-----+
    76. | id | name      | price | qty |
    77. +----+-----------+-------+-----+
    78. |  1 | 香蕉      |   8.5 | 200 |
    79. |  3 | 菠萝      |  12.4 |  70 |
    80. |  2 | 苹果      |  12.5 | 400 |
    81. |  4 | 哈密瓜    |  18.3 | 400 |
    82. +----+-----------+-------+-----+
    83. 4 rows in set (0.00 sec)
    84.  
    85.  
    86. mysql> select max(price) from product;
    87. +------------+
    88. | max(price) |
    89. +------------+
    90. |       18.3 |
    91. +------------+
    92. 1 row in set (0.00 sec)
    93.  
    94. mysql> select min(price) from product;
    95. +------------+
    96. | min(price) |
    97. +------------+
    98. |        8.5 |
    99. +------------+
    100. 1 row in set (0.00 sec)
    101.  
    102. mysql> select sum(price) from product;
    103. +-------------------+
    104. | sum(price)        |
    105. +-------------------+
    106. | 51.69999885559082 |
    107. +-------------------+
    108. 1 row in set (0.01 sec)

  • 相关阅读:
    JVM-JVM中对象的结构
    【服务器搭建】教程四：域名怎样进行备案？快来看~
    PyQt5可视化编程-菜单和工具栏
    扩大图片手势的点击范围的方法
    软件供应链攻击的新形式
    Nginx服务之SSL
    Go语言 Interface(接口)
    常见分布式理论（CAP、BASE）和一致性协议（Gosssip、Raft）
    LeetCode 383 赎金信
    2.X版本的一个通病问题
  • 原文地址：https://blog.csdn.net/weixin_70751333/article/details/140974438