一、MySql安装
-
机器准备：10.117.201.80(从服务器、8核)、10.117.201.81(主服务器、16核)
-
下载mysql安装包:mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
-
上传至两台linux服务器
[root@mon mysql]# pwd /usr/local/mysql [root@mon mysql]# ls mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -
解压
[root@mon mysql]# tar -xzvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql/将其解压到指定的路径，并且通过mv命令修改名字为mysql5.7.30
[root@mon mysql5.7.30]# pwd /usr/local/mysql/mysql5.7.30 [root@mon mysql5.7.30]# ls bin data docs include lib LICENSE man README share support-files -
创建用户组
[root@mon mysql5.7.30] groupadd mysql [root@mon mysql5.7.30] useradd -r -g mysql mysql -
将安装目录所有者及所属组改为mysql
[root@mon mysql5.7.30] chown -R mysql.mysql /usr/local/mysql/mysql5.7.30 -
创建data文件夹，用于存放数据库表之类的数据
[root@mon mysql5.7.30] mkdir data -
初始化
[root@mon mysql5.7.30] /usr/local/mysql/mysql5.7.30/bin/mysqld --user=mysql --basedir=/usr/local/mysql/mysql5.7.30/ --datadir=/usr/local/mysql/mysql5.7.30/data --initialize -
编辑配置文件
linux中mysql的配置文件是/etc/my.cnf、在配置文件中写入以下内容
[mysqld] #这里是开启binlog日志 log-bin=mysql-bin #这里是指主服务器、从服务器跟这个一定不能相同 server-id=1 datadir=/usr/local/mysql/mysql5.7.30/data basedir=/usr/local/mysql/mysql5.7.30 socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 #设置mysql最大连接数 max_connections = 1000 default-time-zone = '+08:00' lower_case_table_names=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # 取消密码验证 # skip-grant-tables [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -
将mysql加入到服务中
[root@mon mysql5.7.30] cp /usr/local/mysql/mysql5.7.30/support-files/mysql.server /etc/init.d/mysql [root@mon mysql5.7.30] chmod +x /etc/init.d/mysql -
设置开机启动
[root@mon mysql5.7.30] chkconfig mysql on -
启动mysql服务
[root@mon mysql5.7.30] service mysql start补充：service mysql stop 停止mysql服务、service mysql restart 重启mysql服务
-
登陆mysql
将配置路径加入到环境变量中,编辑文件
[root@mon mysql5.7.30] vi /etc/profile export PATH=$SPLUNK_HOME/bin:$PATH:/usr/local/mysql/mysql5.7.30/bin 主要是把bin的路径给加进来然后校验生效一下
[root@mon mysql5.7.30] source /etc/profile因为在配置文件中/etc/my.cnf中跳过密码登陆，所以可以免密登陆
[root@mon mysql5.7.30] mysql -uroot -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 5.7.30-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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>
二、主从配置
-
上面是安装mysql的步骤。那么接下来是主从服务器的配置，关于主从服务器的配置，我这里拷贝一份主服务器的/etc/my.cnf文件到从机器80上的相应路径。
[root@mon mysql5.7.30] scp -r /etc/my.cnf 10.117.201.80:/etc/my.cnf -
主节点的先配置
在my.cnf文件中
[mysqld] #开启binlog日志 log-bin=mysql-bin #确定主机id server-id=1 -
从节点配置
在从节点上，必须指定唯一的service id 。可以不为从节点开启二进制日志，如果开启了，也可以用于数据备份和恢复。
[mysqld] server-id=2 -
创建复制用户
从节点需要有一个主节点上面的用户来连接主节点，任何一个有replication slave权限的用户都可以用户复制操作。
登陆到主节点，执行如下命令，创建一个test用户并且授予replication slave权限。
mysql>create user 'test'@'%' identified by 'test' # 上面%为允许外网ip访问，一般内网的不建议这么搞，一般设置成从服务器的ip地址：我这里是10.117.201.80 mysql>grant replication slave on *.* to 'test'@'10.117.201.80'; #分配所有权限给test用户、*.*表示所有数据库可以进行同步，test表示授权用户，10.117.201.80表示这台服务器可以进行同步 mysql>flush privileges #刷新权限 mysql>show master status #查看master的状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 2784 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #mysql-bin.000003 日志名称，2784 日志号 -
为从节点指定复制主节点、登陆从节点操作
mysql>change master to master_host='10.117.201.81',master_user='test',master_password='test',master_log_file='mysql-bin.000003',master_log_pos=2784; # master_host 主节点IP master_user 主节点创建的test复制用户，master_password 用户密码 master_log_file 主节点中的日志名称 master_log_pos 主节点position位置 -
启动从节点同步
mysql>start slave;#如果需要停止从节点同步、可以输入mysql>stop slave命令
-
查看从节点状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2784 Relay_Log_File: mon-longi-micro1-relay-bin.000012 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 2784 Relay_Log_Space: 704 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: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/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)找到上面Slave_IO_Running: Yes、Slave_SQL_Running: Yes说明主从同步配置已经成功，那么需要验证；比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。
-
在主节点test数据库中test表插入一条数据
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | +----+----------+------+ 4 rows in set (0.00 sec) mysql> insert into test values(5,'zhang',26); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec) -
登陆从节点查看是否有同样的数据存在
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec)可见从节点80这台机器确实存在刚才在主节点81上面插入的数据。
-
验证从节点关闭后，主节点数据更新，从节点理论上是不会有数据同步过来的，登陆从节点操作
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3053 Relay_Log_File: mon-longi-micro1-relay-bin.000012 Relay_Log_Pos: 589 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: 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: 3053 Relay_Log_Space: 973 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: 1 Master_UUID: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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)上述可以看到我已经关闭了从节点的同步。
-
登陆主节点更新test库中test表数据。
mysql> update test set name='孙悟空' where id =1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | 孙悟空 | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+-----------+------+ 5 rows in set (0.00 sec)上面可以看到主节点我改了东西。观察从节点test库test表是否有变动。
mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec)可以看到从节点没有变化，结果如我们预期一致。
-
现在我们开启从节点同步
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3345 Relay_Log_File: mon-longi-micro1-relay-bin.000013 Relay_Log_Pos: 612 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 3345 Relay_Log_Space: 1265 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: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/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)Slave_IO_Running: Yes Slave_SQL_Running: Yes可以看到同步是成功的，那么确定一下主节点刚才更新的数据是否已经过来了、查看一下从节点的test库test表。
mysql> select * from test; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | 孙悟空 | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+-----------+------+ 5 rows in set (0.00 sec)确实同步过来了，完美。
-
安装过程报错问题解决
比如在mysql修改密码时报错：
mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.解决方案：
mysql> alter user 'root'@'localhost' identified by '123456'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements #你想要设置一个简单的测试密码的话，比如设置为123456，会提示这个错误，报错的意思就是你的密码不符合要求其实与validate_password_policy的值有关解决方案：
mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) #修改validate_password_policy参数的值 mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) #alidate_password_length(密码长度)参数默认为8，我们修改为1 mysql> alter user 'root'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec)#上述是密码修改问题；mysq5.7.x是需要初始密码的，而初始密码是在初始化的时候会现在命令行显示的。
-
主从数据不一致问题、Slave_SQL_Running:No的两种解决办法
1、程序可能在slave上进行了写操作
2、也可能是slave机器重起后，事务回滚造成的
一般是事务回滚造成的：
解决方法一：mysql> stop slave ; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; #跳过slave上的1个错误 mysql> start slave ; 解决方法二(推荐):
首先停掉slave服务、到主节点查看主机状态、记录File和Position对应值
#进入master mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 3345 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)然后到slave服务器上执行手动同步：
mysql> change master to > master_host='10.117.201.81', > master_user='test', > master_password='test', > master_port=3306, > master_log_file=mysql-bin.000003', > master_log_pos=3345 ; 1 row in set (0.00 sec) mysql> start slave ; 1 row in set (0.00 sec)查看从节点状态
mysql> show slave status\G *************************** 1. row *************************** ........ Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3345 Relay_Log_File: localhost-relay-bin.000537 Relay_Log_Pos: 1034 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:mysql主从复制更多问题解决方案参考如下：
MySQL数据同步，出现Slave_SQL_Running：no和slave_io_running：no问题的解决方法
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
