一)camke源码编译安装mysql
1)创建软件安装目录software
1 [root@master software]# ls2 cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz3 [root@master software]#
2)解压cmak软件
1 [root@master software]# tar xf cmake-2.8.8.tar.gz2 [root@master software]# ls3 cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz4 [root@master software]# cd cmake-2.8.8/
3)在cmake目录下编译
1 [root@master software]# ./configure2 [root@master software]# gmake3 [root@master software]# gmake install4 [root@master software]# cd../
4)安装依赖包
1 [root@master software]# yum install ncurses-devel -y
5)创建mysql组，并创建mysql用户指定mysql组且不能登录
1 [root@master software]# groupadd mysql2 [root@master software]# useradd mysql -s /sbin/nologin -M -g mysql
6)解压mysql，做个软连接
1 [root@slave software]# tar zxf mysql-5.5.32.tar.gz2 [root@slave software]# ls3 cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32 mysql-5.5.32.tar.gz4 [root@slave software]# ln -s mysql-5.5.32mysql#这边一定要注意自己做的软连接，指定的datadir和basedir一定要和/etc/my.conf配置指定的socket指定目录一样，要不socket起不来pid5 [root@slave software]# ls6 cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz7 [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 755mysql_install_db3 [root@master scripts]# ./mysql_install_db --user=mysql --basedir=/home/software/mysql --datadir=/home/software/mysql/data4 Installing MySQL system tables...5 OK6 Filling help tables...7 OK8
9 To start mysqld at boot time you have to copy10 support-files/mysql.server to the right place foryour system11
12 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
13 To doso, 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_installation20
21 which will also give you the option of removing the test22 databases and anonymous user created by default. This is23 strongly recommended forproduction servers.24
25 See the manual formore 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.pl31 cd /home/software/mysql/mysql-test ; perl mysql-test-run.pl32
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/mysqld5 cp: overwrite ‘/etc/init.d/mysqld’?y6 [root@master support-files]# chmod +x /etc/init.d/mysqld
10)启动mysql
1 [root@master /]# /etc/init.d/mysqld start2 Starting MySQL... SUCCESS!
3 [root@master /]# ss -lntup|grep mysql4 tcp LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=6734,fd=11))
到此，主库跟从库mysql就成功安装了
3)让mysql开机自启动，
1 [root@master /]# chkconfig --add mysqld2 [root@master /]# chkconfig mysqld on3 [root@master /]# chkconfig --list mysqld4
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 use11 'systemctl list-dependencies [target]'.#看到这个不谎，因为我的是发行版的centos12
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]# ls3 innochecksum mysqlaccess.conf mysqld mysqlimport mysql_upgrade4 msql2mysql mysqladmin mysqld_multi mysql_plugin mysql_waitpid5 myisamchk mysqlbinlog mysqld_safe mysql_secure_installation mysql_zap6 myisam_ftdump mysqlbug mysqldump mysql_setpermission perror7 myisamlog mysqlcheck mysqldumpslow mysqlshow replace8 myisampack mysql_client_test mysql_embedded mysqlslap resolveip9 my_print_defaults mysql_client_test_embedded mysql_find_rows mysqltest resolve_stack_dump10 mysql mysql_config mysql_fix_extensions mysqltest_embedded11 mysqlaccess mysql_convert_table_format mysqlhotcopy mysql_tzinfo_to_sql12 [root@master bin]# ./mysql13 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 distribution16
17 Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.18
19 Oracle is a registered trademark of Oracle Corporation and/or its20 affiliates. Other names may be trademarks of their respective21 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/profile2 [root@master /]# tail -1 /etc/profile3 export PATH=/home/software/mysql-5.5.32/bin:$PATH4 [root@master /]# source /etc/profile5 [root@master /]# mysql6 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 distribution9
10 Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.11
12 Oracle is a registered trademark of Oracle Corporation and/or its13 affiliates. Other names may be trademarks of their respective14 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 /]# mysql3 ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using password: NO)4 [root@master /]# mysql -uroot -p5 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 distribution9
10 Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.11
12 Oracle is a registered trademark of Oracle Corporation and/or its13 affiliates. Other names may be trademarks of their respective14 owners.15
16 Type 'help;' or '\h' for help. Type '\c'to clear the current input statement.17
18 mysql> Ctrl-C -- exit!
19 Aborted20 [root@master /]#
三)mysql主从复制
1)mysql主从复制基本原理剖析(感谢我白哥配图)
2)确保 server-id 要不同，通常主ID要小于从ID。一定注意
2.1查看主库的id
1 [root@master /]# egrep "log-bin|server-id" /etc/f2 log-bin=mysql-bin3 server-id = 1
4 #server-id = 2
5 #log-bin=mysql-bin6 [root@master /]#
2.2查看从库的id并修改让server-id大于主库
1 [root@slave /]# vim /etc/f2 [root@slave /]# egrep "log-bin|server-id" /etc/f3 log-bin=mysql-bin4 #server-id = 1
5 server-id =2
6 #server-id = 2
7 #log-bin=mysql-bin8 [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的网段可以连接，密码为nick2 Query OK, 0 rows affected (0.01sec)3
4 mysql>flush privileges;#刷新5 Query OK, 0 rows affected (0.02sec)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.00sec)20
21 mysql>
3.2)然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作
1 mysql>flush tables with read lock;#锁表2 Query OK, 0 rows affected (0.01sec)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.00sec)11
12 mysql>
3.3)让大家看看我的二进制日志位置(这个不是必要操作)
1 [root@slave /]# cd /home/software/mysql-5.5.32/data/
2 [root@slave data]# ls3 ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql-bin.index slave.err test4 ib_logfile0 mysql mysql-bin.000002 mysql-bin.000004performance_schema slave.pid5 [root@slave data]#
注意:如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句show master status会显示空，在锁表之后，再导出数据库里的数据(如果数据库里没有数据，可以忽略这一步)
3.4)创建mysql备份目录backup，并开始备份数据，请点击(前面是源码，后面是网上源码总结的)
1 [root@master software]# mkdir backup#创建mysql备份的目录2 [root@master software]# ls3 backup cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz4 [root@master software]# cd /home/software/mysql-5.5.32/sock/#因为用的mysqldump备份，需要指定sock，这就是cmake编译时候指定的路径
5 [root@master sock]# ls6 mysql.sock7 [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.gz9 -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.gz13 Enter password:14 [root@master sock]# cd /home/software/backup/
15 [root@master backup]# ls16 mysql_macc_bak.-06-01.sql.gz mysql_macc_bak.sql.gz17 [root@master backup]# ls18 mysql_macc_bak.-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.1)查看从服务器的sesrver-id
1 [root@slave /]# egrep "log-bin|server-id" /etc/f2 log-bin=mysql-bin3 #server-id = 1
4 server-id =2
5 #server-id = 2
6 #log-bin=mysql-bin7 [root@slave /]#
注意：从服务器上没必要打开log-bin，当然也可以打开log-bin选项，这样可以在从服务器上进行数据备份和灾难恢复，或者让这个从服务器变成一个主服务器。
1 mysql>select user,host from mysql.user;#查看从库的用户macc2 +------+--------------+
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.00sec)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 first23 mysql>stop slave24 ->;25 Query OK, 0 rows affected (0.02sec)26
27 mysql>change master to28 -> 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.02sec)35
36 mysql>start slave;37 Query OK, 0 rows affected (0.00sec)38
39 mysql>show slave status\G40 *************************** 1. row ***************************
41 Slave_IO_State: Waiting formaster to send event42 Master_Host: 192.168.32.132
43 Master_User: macc44 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: None65 Until_Log_File:66 Until_Log_Pos: 0
67 Master_SSL_Allowed: No68 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: No75 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.00sec)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 distribution6
7 Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.8
9 Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective11 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.01sec)25
26 mysql>create database master;27 Query OK, 1 row affected (0.01sec)28
29 mysql>
4.3)从库查看
1 [root@slave software]# mysql -uroot -p2 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 distribution6
7 Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.8
9 Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective11 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.01sec)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.00sec)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.gz2 Enter password:3 [root@slave software]# cd backup/
4 [root@slave backup]# ls5 mysql_macc_bak.-06-01.sql.gz6 [root@slave backup]#
4)Shell脚本监控mysql主从
1 #!/bin/bash2 #Date:-6-19
3 #Author:king4 #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 then12 echo "MySQL is slave is ok"
13 else
14 for ((i=0;i
16 if [ "${array[3]}" = "${errorno[$i]}"];then17 $mysql_cmd -e "stop slave &&set global sql_slave_skip_counter=1;start slave;"
18 fi19 done20 char="MySQL slave is not ok"
21 echo "$char"
22 echo "$char"|mail -s "$char" 838915***@23 break
24 fi25 sleep 30
26 done
4.1)执行结果
1 [root@slave_lamp ~]# sh test.sh2 MySQL is slave isok3 MySQL is slave is ok
次脚本可用于生产案例，如果测试没有收到邮件，不要谎，这个是vps禁用了 25端口问题，欢迎大家留意帮忙解决哈，当然第三方就算了
五)留记录，yum安装mysql5.7
1 rpm -Uvh //mysql80-community-release-el7-2.noarch.rpm #添加mysql yum源
2 yum repolist all |grep mysql #查看yum源中所有Mysql版本3 yum-config-manager --disable mysql80-community #此时的最新版本是mysql8.0，把它禁用掉4 yum-config-manager --enable mysql57-community #mysql5.7是我要安装的版本，启用mysql5.7
5 yum repolist enabled |grep mysql #检查刚才的配置是否生效6 yum install mysql-community-server -y #开始安装7 systemctl start mysqld.service #启动mysql服务8 systemctl status mysqld #检查mysql状态，9 grep 'temporary password' /var/log/mysqld.log10 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; #登录mysql后必须修改密码，密码简单会报错ERROR 1819(HY000): Your password does not satisfy the current policy requirements11 set password for root@localhost = password('root');#这是改mysql密码的passwor函数，修改mysql密码很多种，选一种就可以了12 set global validate_password_policy=0; #修改validate_password_policy参数的值13 set global validate_password_length=1; #再修改密码的长度，然后在执行修改密码就好了14 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; #授权其他机器可以登录
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; #为mysql，root用户增加远程连接能力
