`

Linux环境Mysql环境配置优化及主从配置

mysqlredhatlinuxperformancecentos 
阅读更多
准备2台服务器信息：
服务器IP
 服务器用户名
服务器密码
操作系统
数据库类型
数据库IP
数据库用户名
数据库密码
服务器设置
192.168.3.4
root
123456
Centos7
Mysql
192.168.3.4
root
123456Abc
master
192.168.3.5
root
123456
Redhat7
Mysql
192.168.3.5
root
123456Abc
slave
1.192.168.3.4 上已经安装配置mysql，192.168.3.5按照如下步骤设置mysql：
 
【1】192.168.3.5 上安装mysql
[root@marklin ~]# cd /usr/local/mysql
[root@marklin mysql]# ll
total 580020
-rw-r--r--. 1 root root 593940480 Mar 25 22:50 mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
[root@marklin mysql]# tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.21-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.21-1.el7.x86_64.rpm
mysql-community-common-5.7.21-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.21-1.el7.x86_64.rpm
mysql-community-client-5.7.21-1.el7.x86_64.rpm
mysql-community-server-5.7.21-1.el7.x86_64.rpm
mysql-community-embedded-5.7.21-1.el7.x86_64.rpm
mysql-community-test-5.7.21-1.el7.x86_64.rpm
mysql-community-devel-5.7.21-1.el7.x86_64.rpm
mysql-community-libs-5.7.21-1.el7.x86_64.rpm
[root@marklin mysql]# rpm -qa|grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
mariadb-server-5.5.52-1.el7.x86_64
mariadb-5.5.52-1.el7.x86_64
[root@marklin mysql]# rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64
error: package mariadb-server-5.5.56-2.el7.x86_64 is not installed
[root@marklin mysql]# rpm -e --nodeps mariadb-server-5.5.52-1.el7.x86_64
[root@marklin mysql]# rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64
[root@marklin mysql]# rpm -e --nodeps mariadb-5.5.52-1.el7.x86_64
[root@marklin mysql]# rpm -qa|grep mariadb
[root@marklin mysql]# rpm -ivh mysql-community-common-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.21-1.e################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-libs-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-5.7.21-1.el7################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-devel-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-devel-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-devel-5.7.21-1.el################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-compat-5.7.2################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-embedded-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-embedded-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-embedded-5.7.21-1################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-embedded-devel-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-embedded-devel-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-embedded-devel-5.################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-embedded-compat-5################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-client-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-5.7.21-1.e################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-5.7.21-1.e################################# [100%]
[root@marklin mysql]# yum install perl-JSON.noarch
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
There are no enabled repos.
Run "yum repolist all" to see the repos you have.
To enable Red Hat Subscription Management repositories:
     subscription-manager repos --enable <repo>
To enable custom repositories:
     yum-config-manager --enable <repo>
[root@marklin mysql]# mysql-community-test-5.7.21-1.el7.x86_64.rpm
bash: mysql-community-test-5.7.21-1.el7.x86_64.rpm: command not found...
[root@marklin mysql]# rpm -ivh mysql-community-test-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-test-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    perl(JSON) is needed by mysql-community-test-5.7.21-1.el7.x86_64
[root@marklin mysql]# yum repolist all
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
repolist: 0
[root@marklin mysql]# ll
total 1160144
-rw-r--r--. 1 root root  593940480 Mar 25 22:50 mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 7155 31415  25107316 Dec 28 20:53 mysql-community-client-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    278844 Dec 28 20:53 mysql-community-common-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   3779988 Dec 28 20:53 mysql-community-devel-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  46256768 Dec 28 20:53 mysql-community-embedded-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  24078148 Dec 28 20:53 mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 128571868 Dec 28 20:53 mysql-community-embedded-devel-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   2238596 Dec 28 20:53 mysql-community-libs-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   2115904 Dec 28 20:54 mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  55662616 Dec 28 20:54 mysql-community-minimal-debuginfo-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 171890056 Dec 28 20:54 mysql-community-server-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  15289580 Dec 28 20:54 mysql-community-server-minimal-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 118654584 Dec 28 20:54 mysql-community-test-5.7.21-1.el7.x86_64.rpm
-rw-r--r--. 1 root root      98112 Mar 25 23:03 perl-JSON-2.59-2.el7.noarch.rpm
[root@marklin mysql]# rpm -ivh perl-JSON-2.59-2.el7.noarch.rpm
warning: perl-JSON-2.59-2.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:perl-JSON-2.59-2.el7             ################################# [100%]
[root@marklin mysql]# rpm -ivh mysql-community-test-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-test-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-test-5.7.21-1.el7################################# [100%]
[root@marklin mysql]#
 
【2】配置192.168.3.5
[root@marklin mysql]# systemctl start mysqld.service
[root@marklin mysql]#  service mysqld status
Redirecting to /bin/systemctl status  mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2018-03-25 23:08:51 CST; 4s ago
     Docs: man:mysqld(8)
  Process: 3928 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3842 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3931 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3931 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Mar 25 23:08:33 marklin.com systemd[1]: Starting MySQL Server...
Mar 25 23:08:51 marklin.com systemd[1]: Started MySQL Server.
[root@marklin mysql]# cat /var/log/mysqld.log | grep password
2018-03-25T15:08:45.982603Z 1 [Note] A temporary password is generated for root@localhost: dZ),Jr:vo6Tg
[root@marklin mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@marklin mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.01 sec)
 
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> et global validate_password_length=3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'et global validate_password_length=3' at line 1
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set password for 'root'@'localhost' = password('123456Abc');
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> show variables like  'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 3     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
 
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456Abc' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
 
【3】配置优化：
mysql> set @@global.slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set @@global.log_slow_admin_statements=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global min_examined_row_limit=100;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global log_throttle_queries_not_using_indexes=10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set @@global.log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global log_output='file,table';
Query OK, 0 rows affected (0.04 sec)
 
mysql> set @@global.log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
 
2.Mysql主从配置：
[1]Master主数据库服务器配置
主从配置的数据库：mark-apple-amber,ark-apple-orchid,mark-apple-knight
(1).Xhell5输入：vim /etc/my.cnf，配置内容如下：
#Setting server-id
server-id=1
log-bin=mysql-master-bin
log-bin-index=mysql-master-bin.index
sync_binlog=1                      
binlog-ignore-db=mysql
binlog-do-db=mark-apple-amber,mark-apple-orchid,mark-apple-knight
log-slave-updates
(2)重启master数据库服务，输入：service mysqld restart
[root@marklin ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@marklin ~]#  service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2018-03-25 06:26:43 EDT; 12s ago
     Docs: man:mysqld(8)
  Process: 52432 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 52409 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 52434 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─52434 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Mar 25 06:26:41 marklin.com systemd[1]: Starting MySQL Server...
Mar 25 06:26:43 marklin.com systemd[1]: Started MySQL Server.
[root@marklin ~]#
(3)master数据库创建授权：grant file on *.* to 'root'@'192.168.3.%' identified by '123456Abc';
mysql> grant file on *.* to 'root'@'192.168.3.%' identified by '123456Abc';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
修改密码策略：
set global validate_password_policy=0;
set global validate_password_length=1;
 
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant file on *.* to 'root'@'192.168.3.%' identified by '123456Abc';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
（4）查看master主库日志情况：show master status;
mysql> show master status;
+------------------+----------+-----------------------------------------------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB                                        | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+-----------------------------------------------------+---------------------------------------------+-------------------+
| mysql-bin.000002 |      422 | mark-apple-amber,ark-apple-orchid,mark-apple-knight | performance_schema,information_schema,mysql |                   |
+------------------+----------+-----------------------------------------------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>
 
 
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant file on *.* to 'root'@'192.168.3.%' identified by '123456Abc';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> grant replication slave on *.* to slave@'192.168.3.%' identified by '123456Abc';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> grant replication slave,super,reload on mark-apple-amber.* to 'root'@'192.168.3.%' identified by '123456Abc';
ERROR 1046 (3D000): No database selected
mysql> grant replication slave,super,reload on *.* to 'root'@'192.168.3.%' identified by '123456Abc';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show master status;
+-------------------------+----------+------------------------------------------------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB                                         | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+------------------------------------------------------+------------------+-------------------+
| mysql-master-bin.000001 |     1188 | mark-apple-amber,mark-apple-orchid,mark-apple-knight | mysql            |                   |
+-------------------------+----------+------------------------------------------------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>
[2]Slave从数据库服务器配置
(1).Xhell5输入：vim /etc/my.cnf，配置内容如下：
#Setting server-id
log-bin=mysql-bin
server-id=2
log-bin-index=mysql-bin.index
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
replicate-do-db=mark-apple-amber,ark-apple-orchid,mark-apple-knight
replicate-ignore-db=mysql
log-slave-update
slave-skip-errors=all
slave-net-timeout=60
sync_master_info=1
master-host=192.168.3.4
master-user=root  
master-password=123456Abc
master-port=3306
(2)重启master数据库服务，输入：service mysqld restart
[root@marklin ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@marklin ~]#  service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2018-03-25 06:26:43 EDT; 12s ago
     Docs: man:mysqld(8)
  Process: 52432 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 52409 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 52434 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─52434 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Mar 25 06:26:41 marklin.com systemd[1]: Starting MySQL Server...
Mar 25 06:26:43 marklin.com systemd[1]: Started MySQL Server.
[root@marklin ~]#
(3) 配置连接主服务器的信息：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> change master to master_host='192.168.3.4', master_user='root', master_password='123456Abc', master_log_file='mysql-bin.000002', master_log_pos=422;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
(3) 查看从服务器状态：show slave status \G;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.3.4
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 422
               Relay_Log_File: marklin-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mark-apple-amber,ark-apple-orchid,mark-apple-knight
          Replicate_Ignore_DB: mysql
           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: 422
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@192.168.3.4:3306' - retry-time: 60  retries: 2
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             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: 180326 03:12:45
     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>
 
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> change master to master_host='192.168.3.4', master_user='root', master_password='123456Abc', master_log_file='mysql-master-bin.000001', master_log_pos=1188;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.3.4
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master-bin.000001
          Read_Master_Log_Pos: 1188
               Relay_Log_File: marklin-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-master-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mark-apple-amber,ark-apple-orchid,mark-apple-knight
          Replicate_Ignore_DB: mysql
           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: 1188
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID:
             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>
3.测试Mysql主从配置：
 
分享到：
| 企业级开发之Git使用规范
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Linux配置mysql主从同步

    ##### 3.3 配置主从关系 **第三步：在从服务器上设置主服务器** 1. **登录从服务器(mysqlserverB)** 2. **配置主服务器信息** - 输入以下命令： ```sql CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_...

    Linux系统安装通用MySQL-8.0.33，及主从配置

    mysql配置文件

    mysql5.6安装主从配置

    根据提供的文件信息，本文将详细介绍如何在Linux...以上就是MySQL 5.6在Linux环境下安装及主从配置的详细步骤。通过上述步骤，您可以完成MySQL 5.6的服务安装与基本的主从复制配置，并对数据库的编码格式有一定的了解。

    linux下mysql主从复制

    通过以上的步骤，我们成功地完成了 MySQL 在 Linux 环境下的单向主从复制和双向主从复制的配置。这样的配置不仅提高了数据的安全性，也为数据的备份和恢复提供了极大的便利。同时，对于需要高可用性和负载均衡的应用...

    Linux下MySQL主从同步复制

    在Linux操作系统上，这一过程涉及到一系列步骤，包括安装MySQL服务、配置主从服务器、设置复制参数以及验证复制状态。以下是详细的配置步骤： 1. **检查系统是否安装MySQL服务**： 使用`rpm -qa |grep -i mysql`...

    Linux配置mysql主从

    #### 环境配置 在进行MySQL主从配置之前，首先需要确保有两个可用的MySQL服务器，本文档中的环境设置为： - **MySQL Server A** (主服务器): IP地址为192.168.0.100 - **MySQL Server B** (从服务器): IP地址为192...

    MySQL主从复制配置文档

    MySQL主从复制是一种数据库高可用性和数据冗余的解决方案，它允许数据...以上就是MySQL主从复制的配置及Linux环境下编译安装的基本流程和相关知识，希望对你有所帮助。在实际操作中，务必根据具体环境和需求进行调整。

    Linux下的MySQL安装及主从配置手册

    在Linux环境下安装MySQL 5.7是一个常见的任务，特别是在服务器管理中。这个过程涉及到多个步骤，包括环境准备、软件包安装、配置文件设定以及主从复制的设置。以下是对这些步骤的详细解释： 1. **环境准备**： - ...

    MYSQL数据库主从复制高可用技术改造环境部署方案

    ### MySQL数据库主从复制高可用技术改造环境部署方案 #### 安装部署DRBD DRBD（Distributed Replicated Block Device）是一种分布式复制块设备，主要用于实现数据在两台或多台服务器之间的实时同步，以此来构建高...

    MySQL 主从配置(linux)，mysql-proxy

    MySQL主从配置在Linux环境下的实施是数据库高可用性和数据安全性的重要组成部分，它涉及到数据库的复制功能，使得数据可以从一个服务器（主服务器）实时同步到另一个服务器（从服务器）。这样，即使主服务器出现故障...

    mysql主从配置，

    - **Linux 环境下的配置**：虽然本文主要介绍的是 Windows 环境下的配置，但在 Linux 系统中配置 MySQL 主从也有其独特之处，如使用 `service mysql restart` 来重启服务等。 - **故障排查**：在配置过程中可能会...

    linux下mysql多实例安装部署及主从配置.docx

    ### Linux 下 MySQL 多实例安装部署及主从配置详解 #### 一、多实例安装部署 **背景介绍：** 在 Linux 系统中部署多个 MySQL 实例是非常常见的需求，尤其对于那些需要高可用性和负载均衡的环境。本文将详细介绍...

    MySQL主从复制搭建 需要用到的脚本

    这里提到的“MySQL主从复制搭建 需要用到的脚本”包含了搭建过程中的一些关键配置文件和自动化管理脚本。 1. `app1.cnf`：这是一个配置文件，通常包含MySQL服务器的特定设置，例如服务器ID、连接信息（如主机名、...

    基于Linux的mysql主从配置全过程记录

    以下是基于Linux的MySQL主从配置的详细步骤： 1. **环境准备**： 主服务器（Master）和从服务器（Slave）需要在同一网络环境下，例如，本例中的主服务器IP为192.168.244.128，从服务器IP为192.168.244.130。 2. *...

Magicbox
Global site tag (gtag.js) - Google Analytics