一）camke源码编译安装mysql
1）创建软件安装目录software
1 [root@master software]# ls 2 cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz 3 [root@master software]#
2)解压cmak软件
1 [root@master software]# tar xf cmake-2.8.8.tar.gz 2 [root@master software]# ls 3 cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz 4 [root@master software]# cd cmake-2.8.8/
3)在cmake目录下编译
1 [root@master software]# ./configure 2 [root@master software]# gmake 3 [root@master software]# gmake install 4 [root@master software]# cd../
4)安装依赖包
1 [root@master software]# yum install ncurses-devel -y
5)创建mysql组，并创建mysql用户指定mysql组且不能登录
1 [root@master software]# groupadd mysql 2 [root@master software]# useradd mysql -s /sbin/nologin -M -g mysql
6)解压mysql，做个软连接
1 [root@slave software]# tar zxf mysql-5.5.32.tar.gz 2 [root@slave software]# ls 3 cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32 mysql-5.5.32.tar.gz 4 [root@slave software]# ln -s mysql-5.5.32 mysql #这边一定要注意自己做的软连接，指定的datadir和basedir一定要和/etc/my.conf配置指定的socket指定目录一样，要不socket起不来pid 5 [root@slave software]# ls 6 cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz 7 [root@slave software]#
7)编译安装
1 cmake . -DCMAKE_INSTALL_PREFIX=/home/software/mysql-5.5.32 \ 2 -DMYSQL_DATADIR=/home/software/mysql-5.5.32/data \ 3 -DMYSQL_UNIX_ADDR=/home/software/mysql-5.5.32/sock/mysql.sock \ # mysql的sock后面用mysqldump备份的时候会用到 4 -DDEFAULT_CHARSET=utf8 \ 5 -DDEFAULT_COLLATION=utf8_general_ci \ 6 -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ 7 -DENABLED_LOCAL_INFILE=ON \ 8 -DWITH_INNOBASE_STORAGE_ENGINE=1 \ 9 -DWITH_FEDERATED_STORAGE_ENGINE=1 \ 10 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ 11 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ 12 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ 13 -DWITH_FAST_MUTEXES=1 \ 14 -DWITH_ZLIB=bundled \ 15 -DENABLED_LOCAL_INFILE=1 \ 16 -DWITH_READLINE=1 \ 17 -DWITH_EMBEDDED_SERVER=1 \ 18 -DWITH_DEBUG=0
1 [root@master mysql-5.5.32]# make&make install
cmake编译安装mysql 到此结束。
二）初始化数据库
1)指定mysql的数据路径datadir，和mysql 的安装路径basedir
1 [root@master mysql-5.5.32]# cd scripts/ 2 [root@master scripts]# chmod 755 mysql_install_db 3 [root@master scripts]# ./mysql_install_db --user=mysql --basedir=/home/software/mysql --datadir=/home/software/mysql/data 4 Installing MySQL system tables... 5 OK 6 Filling help tables... 7 OK 8 9 To start mysqld at boot time you have to copy 10 support-files/mysql.server to the right place for your system 11 12 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 13 To do so, start the server, then issue the following commands: 14 15 /home/software/mysql/bin/mysqladmin -u root password 'new-password' 16 /home/software/mysql/bin/mysqladmin -u root -h master password 'new-password' 17 18 Alternatively you can run: 19 /home/software/mysql/bin/mysql_secure_installation 20 21 which will also give you the option of removing the test 22 databases and anonymous user created by default. This is 23 strongly recommended for production servers. 24 25 See the manual for more instructions. 26 27 You can start the MySQL daemon with: 28 cd /home/software/mysql ; /home/software/mysql/bin/mysqld_safe & 29 30 You can test the MySQL daemon with mysql-test-run.pl 31 cd /home/software/mysql/mysql-test ; perl mysql-test-run.pl 32 33 Please report any problems with the /home/software/mysql/scripts/mysqlbug script! 34 35 [root@master scripts]#
2)拷贝mysql 的启动脚本，并授权脚本权限
1 [root@master support-files]# cd .. 2 [root@master mysql-5.5.32]# cd scripts/ 3 [root@master scripts]# cd ../support-files/ 4 [root@master support-files]# cp mysql.server /etc/init.d/mysqld 5 cp: overwrite ‘/etc/init.d/mysqld’? y 6 [root@master support-files]# chmod +x /etc/init.d/mysqld
10)启动mysql
1 [root@master /]# /etc/init.d/mysqld start 2 Starting MySQL... SUCCESS! 3 [root@master /]# ss -lntup|grep mysql 4 tcp LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=6734,fd=11))
到此，主库跟从库mysql就成功安装了
3)让mysql开机自启动，
1 [root@master /]# chkconfig --add mysqld 2 [root@master /]# chkconfig mysqld on 3 [root@master /]# chkconfig --list mysqld 4 5 Note: This output shows SysV services only and does not include native 6 systemd services. SysV configuration data might be overridden by native 7 systemd configuration. 8 9 If you want to list systemd services use 'systemctl list-unit-files'. 10 To see services enabled on particular target use 11 'systemctl list-dependencies [target]'. #看到这个不谎，因为我的是发行版的centos 12 13 mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
4)配置mysql命令的全局使用路径
1 [root@master mysql-5.5.32]# cd bin/ 2 [root@master bin]# ls 3 innochecksum mysqlaccess.conf mysqld mysqlimport mysql_upgrade 4 msql2mysql mysqladmin mysqld_multi mysql_plugin mysql_waitpid 5 myisamchk mysqlbinlog mysqld_safe mysql_secure_installation mysql_zap 6 myisam_ftdump mysqlbug mysqldump mysql_setpermission perror 7 myisamlog mysqlcheck mysqldumpslow mysqlshow replace 8 myisampack mysql_client_test mysql_embedded mysqlslap resolveip 9 my_print_defaults mysql_client_test_embedded mysql_find_rows mysqltest resolve_stack_dump 10 mysql mysql_config mysql_fix_extensions mysqltest_embedded 11 mysqlaccess mysql_convert_table_format mysqlhotcopy mysql_tzinfo_to_sql 12 [root@master bin]# ./mysql 13 Welcome to the MySQL monitor. Commands end with ; or \g. 14 Your MySQL connection id is 1 15 Server version: 5.5.32-log Source distribution 16 17 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 18 19 Oracle is a registered trademark of Oracle Corporation and/or its 20 affiliates. Other names may be trademarks of their respective 21 owners. 22 23 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 24 25 mysql>
4.1)把mysql命令添加到全局变量
1 [root@master /]# echo 'export PATH=/home/software/mysql-5.5.32/bin:$PATH' >>/etc/profile 2 [root@master /]# tail -1 /etc/profile 3 export PATH=/home/software/mysql-5.5.32/bin:$PATH 4 [root@master /]# source /etc/profile 5 [root@master /]# mysql 6 Welcome to the MySQL monitor. Commands end with ; or \g. 7 Your MySQL connection id is 5 8 Server version: 5.5.32-log Source distribution 9 10 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 11 12 Oracle is a registered trademark of Oracle Corporation and/or its 13 affiliates. Other names may be trademarks of their respective 14 owners. 15 16 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 17 18 mysql>
4.2）设置密码
1 [root@master /]# mysqladmin -u root password 'root' 2 [root@master /]# mysql 3 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 4 [root@master /]# mysql -uroot -p 5 Enter password: 6 Welcome to the MySQL monitor. Commands end with ; or \g. 7 Your MySQL connection id is 8 8 Server version: 5.5.32-log Source distribution 9 10 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 11 12 Oracle is a registered trademark of Oracle Corporation and/or its 13 affiliates. Other names may be trademarks of their respective 14 owners. 15 16 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 17 18 mysql> Ctrl-C -- exit! 19 Aborted 20 [root@master /]#
三）mysql主从复制
1）mysql主从复制基本原理剖析（感谢我白哥配图）
2）确保 server-id 要不同，通常主ID要小于从ID。一定注意
2.1查看主库的id
1 [root@master /]# egrep "log-bin|server-id" /etc/my.cnf 2 log-bin=mysql-bin 3 server-id = 1 4 #server-id = 2 5 #log-bin=mysql-bin 6 [root@master /]#
2.2查看从库的id并修改让server-id大于主库
1 [root@slave /]# vim /etc/my.cnf 2 [root@slave /]# egrep "log-bin|server-id" /etc/my.cnf 3 log-bin=mysql-bin 4 #server-id = 1 5 server-id =2 6 #server-id = 2 7 #log-bin=mysql-bin 8 [root@slave /]#
3）主服务器操作
3.1）首先登陆数据库，创建macc（master salve connect common）主从复制专用账户，
1 mysql> grant replication slave on *.* to 'macc'@'192.168.32.%' identified by 'nick'; #创建macc账户并授权192.168.32的网段可以连接，密码为nick 2 Query OK, 0 rows affected (0.01 sec) 3 4 mysql> flush privileges; #刷新 5 Query OK, 0 rows affected (0.02 sec) 6 7 mysql> select user,host from mysql.user; 查看macc用户 8 +------+--------------+ 9 | user | host | 10 +------+--------------+ 11 | root | 127.0.0.1 | 12 | macc | 192.168.32.% | 13 | root | ::1 | 14 | | localhost | 15 | root | localhost | 16 | | slave | 17 | root | slave | 18 +------+--------------+ 19 7 rows in set (0.00 sec) 20 21 mysql>
3.2)然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作
1 mysql> flush tables with read lock; #锁表 2 Query OK, 0 rows affected (0.01 sec) 3 4 mysql> show master status; #获取二进制日志信息我的可以到/home/software/mysql-5.5.32/data/mysql-bin. 找到，默认都是在data目录下。 5 +------------------+----------+--------------+------------------+ 6 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |#flie的值是当前二进制日志的文件名，position是该日志的位置信息，会在配置从时候用到 7 +------------------+----------+--------------+------------------+ 8 | mysql-bin.000004 | 471 | | | 9 +------------------+----------+--------------+------------------+ 10 1 row in set (0.00 sec) 11 12 mysql>
3.3)让大家看看我的二进制日志位置（这个不是必要操作）
1 [root@slave /]# cd /home/software/mysql-5.5.32/data/ 2 [root@slave data]# ls 3 ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql-bin.index slave.err test 4 ib_logfile0 mysql mysql-bin.000002 mysql-bin.000004 performance_schema slave.pid 5 [root@slave data]#
注意:如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句show master status会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）
3.4）创建mysql备份目录backup，并开始备份数据，mysqldump备份命令请点击（前面是源码，后面是网上源码总结的）
1 [root@master software]# mkdir backup #创建mysql备份的目录 2 [root@master software]# ls 3 backup cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz 4 [root@master software]# cd /home/software/mysql-5.5.32/sock/ #因为用的mysqldump备份，需要指定sock，这就是cmake编译时候指定的路径 5 [root@master sock]# ls 6 mysql.sock 7 [root@master sock]# 8 [root@master sock]# mysqldump -uroot -p -S /home/software/mysql-5.5.32/sock/mysql.sock --all-databases | gzip >/home/software/backup/mysql_macc_bak$(date+%F).sql.gz 9 -bash: date+%F: command not found # 这里报错是因为$(date +%F)需要有空格，且前面我没加点导致的 10 Enter password: 11 -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. 12 [root@master sock]# mysqldump -uroot -p -S /home/software/mysql-5.5.32/sock/mysql.sock --all-databases --events | gzip >/home/software/backup/mysql_macc_bak.$(date +%F).sql.gz 13 Enter password: 14 [root@master sock]# cd /home/software/backup/ 15 [root@master backup]# ls 16 mysql_macc_bak.2019-06-01.sql.gz mysql_macc_bak.sql.gz 17 [root@master backup]# ls 18 mysql_macc_bak.2019-06-01.sql.gz mysql_macc_bak.sql.gz
3.5)这个时候就可以解锁表了
1 mysql> unlock tables; 2 Query OK, 0 rows affected (0.00 sec)
4）从服务器配置
4.1)查看从服务器的sesrver-id
1 [root@slave /]# egrep "log-bin|server-id" /etc/my.cnf 2 log-bin=mysql-bin 3 #server-id = 1 4 server-id =2 5 #server-id = 2 6 #log-bin=mysql-bin 7 [root@slave /]#
注意：从服务器上没必要打开log-bin，当然也可以打开log-bin选项，这样可以在从服务器上进行数据备份和灾难恢复，或者让这个从服务器变成一个主服务器。
1 mysql> select user,host from mysql.user; #查看从库的用户macc 2 +------+--------------+ 3 | user | host | 4 +------+--------------+ 5 | root | 127.0.0.1 | 6 | macc | 192.168.32.% | 7 | root | ::1 | 8 | | localhost | 9 | root | localhost | 10 | | slave | 11 | root | slave | 12 +------+--------------+ 13 7 rows in set (0.00 sec) 14 15 mysql> change master to #参数配置见下面的详细解释 16 -> MASTER_HOST='192.168.32.132', 17 -> MASTER_PORT=3306, 18 -> MASTER_USER='macc', 19 -> MASTER_PASSWORD='nick', 20 -> MASTER_LOG_FILE='mysql-bin.000004', 21 -> MASTER_LOG_POS=245; 22 ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first 23 mysql> stop slave 24 -> ; 25 Query OK, 0 rows affected (0.02 sec) 26 27 mysql> change master to 28 -> MASTER_HOST='192.168.32.132', 29 -> MASTER_PORT=3306, 30 -> MASTER_USER='macc', 31 -> MASTER_PASSWORD='nick', 32 -> MASTER_LOG_FILE='mysql-bin.000004', 33 -> MASTER_LOG_POS=245; 34 Query OK, 0 rows affected (0.02 sec) 35 36 mysql> start slave; 37 Query OK, 0 rows affected (0.00 sec) 38 39 mysql> show slave status\G 40 *************************** 1. row *************************** 41 Slave_IO_State: Waiting for master to send event 42 Master_Host: 192.168.32.132 43 Master_User: macc 44 Master_Port: 3306 45 Connect_Retry: 60 46 Master_Log_File: mysql-bin.000005 47 Read_Master_Log_Pos: 107 48 Relay_Log_File: slave-relay-bin.000003 49 Relay_Log_Pos: 253 50 Relay_Master_Log_File: mysql-bin.000005 51 Slave_IO_Running: Yes #io进程负责从库去主库读取二进制日志，并写到从库的中继日志中，io跟sql进程出现no，从服务器要跟主服务器通，server-id要不一样 52 Slave_SQL_Running: Yes #sql进程负责将中继日志换成sql语句后执行 53 Replicate_Do_DB: 54 Replicate_Ignore_DB: 55 Replicate_Do_Table: 56 Replicate_Ignore_Table: 57 Replicate_Wild_Do_Table: 58 Replicate_Wild_Ignore_Table: 59 Last_Errno: 0 60 Last_Error: 61 Skip_Counter: 0 62 Exec_Master_Log_Pos: 107 63 Relay_Log_Space: 856 64 Until_Condition: None 65 Until_Log_File: 66 Until_Log_Pos: 0 67 Master_SSL_Allowed: No 68 Master_SSL_CA_File: 69 Master_SSL_CA_Path: 70 Master_SSL_Cert: 71 Master_SSL_Cipher: 72 Master_SSL_Key: 73 Seconds_Behind_Master: 0 74 Master_SSL_Verify_Server_Cert: No 75 Last_IO_Errno: 0 76 Last_IO_Error: 77 Last_SQL_Errno: 0 78 Last_SQL_Error: 79 Replicate_Ignore_Server_Ids: 80 Master_Server_Id: 1 81 1 row in set (0.00 sec) 82 83 mysql>
4.2)验证主从是否完成
在主库上创建master数据库，到从库查看，
1 [root@master backup]# mysql -uroot -p #这个是主库 2 Enter password: 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 3 5 Server version: 5.5.32-log Source distribution 6 7 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 8 9 Oracle is a registered trademark of Oracle Corporation and/or its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15 mysql> show databases; 16 +--------------------+ 17 | Database | 18 +--------------------+ 19 | information_schema | 20 | mysql | 21 | performance_schema | 22 | test | 23 +--------------------+ 24 4 rows in set (0.01 sec) 25 26 mysql> create database master; 27 Query OK, 1 row affected (0.01 sec) 28 29 mysql>
4.3）从库查看
1 [root@slave software]# mysql -uroot -p 2 Enter password: 3 Welcome to the MySQL monitor. Commands end with ; or \g. 4 Your MySQL connection id is 11 5 Server version: 5.5.32-log Source distribution 6 7 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 8 9 Oracle is a registered trademark of Oracle Corporation and/or its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 14 15 mysql> show databases; 16 +--------------------+ 17 | Database | 18 +--------------------+ 19 | information_schema | 20 | mysql | 21 | performance_schema | 22 | test | 23 +--------------------+ 24 4 rows in set (0.01 sec) 25 26 mysql> show databases; #可以看到在mysql主上创建的master数据库有了， 27 +--------------------+ 28 | Database | 29 +--------------------+ 30 | information_schema | 31 | master | 32 | mysql | 33 | performance_schema | 34 | test | 35 +--------------------+ 36 5 rows in set (0.00 sec) 37 38 mysql>
4.4)到此mysql主从完成，在mysql从服务器上做一个备份（这边我没有关闭slave从服务器，应该先关闭从服务器备份，在启动，这样保证备份数据的完整性。）
1 [root@slave software]# mysqldump -uroot -p -S /home/software/mysql-5.5.32/sock/mysql.sock --all-databases --events | gzip >/home/software/backup/mysql_macc_bak.$(date +%F).sql.gz 2 Enter password: 3 [root@slave software]# cd backup/ 4 [root@slave backup]# ls 5 mysql_macc_bak.2019-06-01.sql.gz 6 [root@slave backup]#
4）Shell脚本监控mysql主从
1 #!/bin/bash 2 #Date:2019-6-19 3 #Author:king 4 #version 1.0 5 mysql_cmd="mysql -u root -proot" 6 errorno=(1158 1159 1008 1007 1062) 7 while true 8 do 9 array=($($mysql_cmd -e "show slave status\G"|egrep '_Running|Behind_Master|Last_SQL_Errno'|awk '{print $NF}')) 10 if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "0" ] 11 then 12 echo "MySQL is slave is ok" 13 else 14 for ((i=0;i<${#errorno[*]};i++)) 15 do 16 if [ "${array[3]}" = "${errorno[$i]}" ];then 17 $mysql_cmd -e "stop slave &&set global sql_slave_skip_counter=1;start slave;" 18 fi 19 done 20 char="MySQL slave is not ok" 21 echo "$char" 22 echo "$char"|mail -s "$char" 838915***@qq.com 23 break 24 fi 25 sleep 30 26 done
4.1）执行结果
1 [root@slave_lamp ~]# sh test.sh 2 MySQL is slave is ok 3 MySQL is slave is ok
次脚本可用于生产案例，如果测试没有收到邮件，不要谎，这个是vps禁用了 25端口问题，欢迎大家留意帮忙解决哈，当然第三方就算了
144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
