MySQL的安装

MySql

通常意见上的mysql安装都是安装的MySQL Community Server

可以通过如下地址下载mysql版本，这里分别就mysql5.7 和mysql8.0分别做说明，安装都采用压缩包形式，非msi。

https://dev.mysql.com/downloads/mysql/

windows

• mysql5.7

点击上方网址，默认打开的是mysql当前最新版本的下载页面，如果需要下载mysql5.7,点击Looking for previous GA version,就会切换5.7版本的下载了。

1. 下载zip包到本地目录。

2. 解压到D:\Servers\mysql5.7目录下。

   
   

   

   
   

3. 创建my.ini文件，图中我已经创建好了。内容如下，这只是一般配置，如果有特殊要求，可以自己再加。

    [mysql]
    #设置mysql客户端默认字符集
    default-character-set=utf8
    [mysqld]
    #设置3306端口
    port=3306
    #设置mysql的安装目录
    basedir=D:\Server\mysql-5.7.33
    #设置mysql数据库的数据的存放目录
    datadir=D:\Server\mysql-5.7.33\data
    #允许最大连接数
    max_connections=200
    #服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    #创建新表时将使用的默认存储引擎
    explicit_defaults_for_timestamp=true
    default-storage-engine=INNODB

4. 配置mysql的环境变量，如图所示。

   
   

   

   
   

5. 在Mysql安装目录下，打开cmd执行窗口(路径栏输入cmd,回车)，执行如下命令:

    # 初始化数据库
    mysqld -install
    mysqld --initialize --console

6. 执行完成后，cmd会输出当前数据库的连接密码，通过这个密码才能连接上数据库。

   
   

7. 执行net start mysql启动服务，确认服务启动成功后，执行mysql -u root -p,输入刚才的密码

8. 修改默认密码

    alter user 'root'@'localhost' identified by '123456'
    flush privileges
    quit
    mysql -u root -p 
    输入 新密码

   自此，mysql5.7的所有安装工作就结束了。

• mysql 8.0

  1，2，3的步骤与5.7版本相差不大，需要注意的是mysql8会自动生成data文件夹，所以不需要手动创建该文件夹，创建了可能报错。

  
  

  还需要注意的一点是，mysql8版本修改了加密方式，如果不修改，会导致部分客户端连接不上，报错caching_sha2_password。

  因此在8步骤时，修改密码的流程不太一样。

   ALTER USER USER() IDENTIFIED BY '123456';       # 修改密码为root
   ​
   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';        #host_address为主机地址，本地为localhost，
   FLUSH PRIVILEGES;                   #刷新

  
  

• 安装常见错误

  1. 

     mysql目录中的data目录不为空

  2. Install/Remove of the Service Denied!

     没有使用管理员身份运行cmd

  3. 启动MySQL时，报发生系统错误2

     之前安装mysql服务没有移除。

Linux

Linux默认安装系统为 centos7

centos7及以上版本 默认安装了MariaDB，所以需要先卸载 MariDB

1. 卸载MariaDB

    yum list installed | grep mariadb # 查询已安装mariadb
    ​
    yum -y remove mariadb* #移除已安装的mariadb
    ​
    yum list installed | grep mariadb # 在查询一遍，确保卸载干净了

2. 下载RPM

    cd /usr/local
    mkdir mysql
    cd mysql
    wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

3. 安装rpm包，并确认yum源

    rpm -ivh mysql57-community-release-el7-11.noarch.rpm 

4. 查看mysql版本

    yum repolist all | grep mysql

5. 安装mysql

    yum install mysql-community-server

6. 启动mysql

    systemctl start mysqld

7. 查找初次的密码

    mysql密码一般存在 /var/log/mysqld.log文件中
    cat /var/log/mysqld.log | grep temporary # 查找临时密码
    ​
    mysql -u root -p # 输入密码，登录mysql

8. 修改默认密码

    mysql 5.7开始密码不能太过简单，否则无法修改成功，因此需要先修改全局变量
    ​
    mysql> set global validate_password_policy=LOW;
    mysql> set global validate_password_length=0;
    ​
    # 修改密码
    ALTER USER USER() IDENTIFIED BY 'wingkin45';#wingkin为密码
    ​
    flush privileges;

9. 配置远程访问

    # 登录mysql
    use mysql;
    ​
    update user set host='%' where user='root';
    ​
    flush privileges;

   自此mysql安装成功。

10. 卸载mysql

     yum -y remove mysql* #移除已安装的mysql

主从复制

前提条件：搭建好两台mysql，并且保证两台服务可以互相ping通，并且开放了3306端口

master: 192.168.140.101

slave: 192.168.140.102

能够互相ping通

配置master

先停止当前mysql，systemctl stop mysqld

执行vim /etc/my.cnf,修改mysql配置文件

 # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 ​
 [mysqld]
 #
 # Remove leading # and set to the amount of RAM for the most important data
 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
 # innodb_buffer_pool_size = 128M
 #
 # Remove leading # to turn on a very important data integrity option: logging
 # changes to the binary log between backups.
 ​
 # 配置 binlog的存储位置,这个位置需要提前创建
 log_bin=/var/lib/mysql/data/binlog/mysql-bin
 # 配置服务器id,必须唯一
 server-id=1
 # 配置同步写入策略，每次写入都同步
 sync-binlog=1
 # 配置 同步的数据库，多个可以配置多行记录
 binlog-do-db=fanco
 # 二进制日志保留时间
 expire-logs-days=7
 ​
 # 不备份的数据库
 binlog-ignore-db=information_schema
 binlog-ignore-db=performation_schema
 binlog-ignore-db=sys
 ​
 #
 # Remove leading # to set options mainly useful for reporting servers.
 # The server defaults are faster for transactions and fast SELECTs.
 # Adjust sizes as needed, experiment to find the optimal values.
 # join_buffer_size = 128M
 # sort_buffer_size = 2M
 # read_rnd_buffer_size = 2M
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 ​
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0
 ​
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

修改好my.cnf文件后，执行systemctl start mysqld

尝试重启mysql,不出意外的话，会报错。

 # 错误1
 mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory)
 ​
 ps: 这里是因为 我们配置的 /var/lib/mysql/data/binlog/这个文件夹的权限并没有给mysql，导致mysql没有权限创建这个文件
 解决方案：
 chown mysql:mysql -R /var/lib/mysql
 ​
 # 错误2
 mysql报错Errcode: 13 "Permission denied"
 ​
 ps: 这里是在处理从库 relay-log 中转日志爆出的这个错误
 解决方案：
 chmod -R 777 /var/lib/mysql

启动mysql成功后，登入查看

 -- 执行show master status;
 mysql> show master status;
 +------------------+----------+--------------+--------------------------------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                           | Executed_Gtid_Set |
 +------------------+----------+--------------+--------------------------------------------+-------------------+
 | mysql-bin.000001 |     1032 | fanco        | information_schema,performation_schema,sys |                   |
 +------------------+----------+--------------+--------------------------------------------+-------------------+
 ​
 file表示binlog文件，position表示 slave服务器读取的位置，binlog_do_db表示 复制的数据库
 binlog_ignore_db 表示 不复制的数据库

为master主机设置一个复制账号（user=aaaa,pwd=123456），并且授权

 -- 1. 去除密码插件
 uninstall plugin validate_password;
 -- 2. 创建新用户
 CREATE USER 'aaaa'@'192.168.%.%' IDENTIFIED BY '123456';  # 用户和密码 自定义
 -- 3. 分配复制权限   *.* 所有数据库的所有表
 grant replication slave,replication client on *.* to aaaa@'192.168.%.%' identified by '123456';  
 -- 4. 刷新权限
 flush privileges;
 ​
 '192.168.%.%'  表示这个授权用户从哪台服务器host能够登录

配置 slave

修改my.cnf

 # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 ​
 [mysqld]
 #
 # Remove leading # and set to the amount of RAM for the most important data
 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
 # innodb_buffer_pool_size = 128M
 #
 # Remove leading # to turn on a very important data integrity option: logging
 # changes to the binary log between backups.
 # log_bin
 server-id=2
 ​
 relay_log=/var/lib/mysql/data/binlog/mysql-relay-bin
 replicate-do-db=fanco
 ​
 read_only=1
 log_slave_updates=1
 # Remove leading # to set options mainly useful for reporting servers.
 # The server defaults are faster for transactions and fast SELECTs.
 # Adjust sizes as needed, experiment to find the optimal values.
 # join_buffer_size = 128M
 # sort_buffer_size = 2M
 # read_rnd_buffer_size = 2M
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 ​
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0
 ​
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid
 ​

启动从服务器的复制线程

1. 先在主节点执行下show master status;

    mysql> show master status;
    +------------------+----------+--------------+--------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                           | Executed_Gtid_Set |
    +------------------+----------+--------------+--------------------------------------------+-------------------+
    | mysql-bin.000001 |     1680 | fanco        | information_schema,performation_schema,sys |                   |
    +------------------+----------+--------------+--------------------------------------------+-------------------+

2. 在slave节点执行如下命令：

    -- 绑定主服务器
    change master to master_host='192.168.140.101',
        > master_user='fancocp',
        > master_password='123456',
        > master_log_file='mysql-bin.000001',
        > master_log_pos=1680;
        
        
    此处有报错，需要 chmod 设置权限
    -- 启动复制线程
    start slave;
    如果一切正常，主从复制就会启动了
    ​
    2021-12-21T12:12:01.904977Z 9 [Note] Slave I/O thread for channel '': connected to master 'fancocp@192.168.140.101:3306',replication started in log 'mysql-bin.000001' at position 1032
    ​

3. 在主数据库增加一个记录，看从数据库是否会将数据同步过来。

4. 查看从数据库状态

    mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.140.101 # 主服务器ip
                      Master_User: fancocp # 用于同步的数据库用户
                      Master_Port: 3306 # 端口
                    Connect_Retry: 60   # 重试时间
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 3904
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 3192
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: fanco
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 3904
                  Relay_Log_Space: 3399
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: f65ef6f1-623d-11ec-9502-000c291f0bc2
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    ​
    ERROR: 
    No query specified
    ​

   至此，mysql主从复制就算是配置好了

binlog相关的命令

 # 查看是否开启binlog
 mysql>show binary logs;
 ​
 #查看binlog格式：
 mysql>show variables like 'binlog_format';
 ​
 #获取binlog文件列表：
 mysql>show binary logs;
 ​
 #查看当前正在写入的binlog文件：
 mysql>show master status;
 ​
 #查看master上的binlog：
 mysql>show master logs;
 ​
 #只查看第一个binlog文件的内容：
 mysql>show binlog events;
 ​
 #查看指定binlog文件的内容：
 mysql>show binlog events in 'mysql-bin.000045';
 ​
 ​
 #清空所有的bin-log：
 mysql>reset master;
 ​
 #生成一个新的binlog：
 mysql>flush logs;

用bash查看binlog日志

 mysqlbinlog mysql-bin.000045

Docker安装

 docker pull mysql:5.7 // 下载镜像
 docker images // 查看镜像
 // 运行mysql容器
 docker run -p 3306:3306 --name mysql \
 -v /mydata/mysql/log:/var/log/mysql \
 -v /mydata/mysql/data:/var/lib/mysql \
 -v /mydata/mysql/conf:/etc/mysql \
 -e MYSQL_ROOT_PASSWORD=root \
 -d mysql:5.7
 ​
 // 查看mysql是否运行
 docker ps