一）camke源码编译安装mysql
1）创建软件安装目录software
[root@master software]# ls
cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz
[root@master software]#
2)解压cmak软件
[root@master software]# tar xf cmake-2.8.8.tar.gz
[root@master software]# ls
cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz
[root@master software]# cd cmake-2.8.8/
3)在cmake目录下编译
[root@master software]# ./configure
[root@master software]# gmake
[root@master software]# gmake install
[root@master software]# cd../
4)安装依赖包
[root@master software]# yum install ncurses-devel -y
5)创建mysql组，并创建mysql用户指定mysql组且不能登录
[root@master software]# groupadd mysql
[root@master software]# useradd mysql -s /sbin/nologin -M -g mysql
6)解压mysql，做个软连接
[root@slave software]# tar zxf mysql-5.5.32.tar.gz
[root@slave software]# ls
cmake-2.8.8 cmake-2.8.8.tar.gz mysql-5.5.32 mysql-5.5.32.tar.gz
[root@slave software]# ln -s mysql-5.5.32 mysql #这边一定要注意自己做的软连接，指定的datadir和basedir一定要和/etc/my.conf配置指定的socket指定目录一样，要不socket起不来pid
[root@slave software]# ls
cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz
[root@slave software]#
7)编译安装
cmake . -DCMAKE_INSTALL_PREFIX=/home/software/mysql-5.5.32 \
-DMYSQL_DATADIR=/home/software/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/home/software/mysql-5.5.32/sock/mysql.sock \ # mysql的sock后面用mysqldump备份的时候会用到 -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
[root@master mysql-5.5.32]# make&make install
cmake编译安装mysql 到此结束。
二）初始化数据库
1)指定mysql的数据路径datadir，和mysql 的安装路径basedir
[root@master mysql-5.5.32]# cd scripts/
[root@master scripts]# chmod 755 mysql_install_db
[root@master scripts]# ./mysql_install_db --user=mysql --basedir=/home/software/mysql --datadir=/home/software/mysql/data
Installing MySQL system tables...
OK
Filling help tables...
OK To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands: /home/software/mysql/bin/mysqladmin -u root password 'new-password'
/home/software/mysql/bin/mysqladmin -u root -h master password 'new-password' Alternatively you can run:
/home/software/mysql/bin/mysql_secure_installation which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with:
cd /home/software/mysql ; /home/software/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl
cd /home/software/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems with the /home/software/mysql/scripts/mysqlbug script! [root@master scripts]#
2)拷贝mysql 的启动脚本，并授权脚本权限
[root@master support-files]# cd ..
[root@master mysql-5.5.32]# cd scripts/
[root@master scripts]# cd ../support-files/
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
cp: overwrite ‘/etc/init.d/mysqld’? y
[root@master support-files]# chmod +x /etc/init.d/mysqld
10)启动mysql
[root@master /]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!
[root@master /]# ss -lntup|grep mysql
tcp LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=6734,fd=11))
到此，主库跟从库mysql就成功安装了
3)让mysql开机自启动，
[root@master /]# chkconfig --add mysqld
[root@master /]# chkconfig mysqld on
[root@master /]# chkconfig --list mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'. #看到这个不谎，因为我的是发行版的centos mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
4)配置mysql命令的全局使用路径
[root@master mysql-5.5.32]# cd bin/
[root@master bin]# ls
innochecksum mysqlaccess.conf mysqld mysqlimport mysql_upgrade
msql2mysql mysqladmin mysqld_multi mysql_plugin mysql_waitpid
myisamchk mysqlbinlog mysqld_safe mysql_secure_installation mysql_zap
myisam_ftdump mysqlbug mysqldump mysql_setpermission perror
myisamlog mysqlcheck mysqldumpslow mysqlshow replace
myisampack mysql_client_test mysql_embedded mysqlslap resolveip
my_print_defaults mysql_client_test_embedded mysql_find_rows mysqltest resolve_stack_dump
mysql mysql_config mysql_fix_extensions mysqltest_embedded
mysqlaccess mysql_convert_table_format mysqlhotcopy mysql_tzinfo_to_sql
[root@master bin]# ./mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, 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>
4.1)把mysql命令添加到全局变量
[root@master /]# echo 'export PATH=/home/software/mysql-5.5.32/bin:$PATH' >>/etc/profile
[root@master /]# tail -1 /etc/profile
export PATH=/home/software/mysql-5.5.32/bin:$PATH
[root@master /]# source /etc/profile
[root@master /]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, 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>
4.2）设置密码
[root@master /]# mysqladmin -u root password 'root'
[root@master /]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@master /]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, 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> Ctrl-C -- exit!
Aborted
[root@master /]#
三）mysql主从复制
1）mysql主从复制基本原理剖析（感谢我白哥配图）
2）确保 server-id 要不同，通常主ID要小于从ID。一定注意
2.1查看主库的id
[root@master /]# egrep "log-bin|server-id" /etc/my.cnf
log-bin=mysql-bin
server-id = 1
#server-id = 2
#log-bin=mysql-bin
[root@master /]#
2.2查看从库的id并修改让server-id大于主库
[root@slave /]# vim /etc/my.cnf
[root@slave /]# egrep "log-bin|server-id" /etc/my.cnf
log-bin=mysql-bin
#server-id = 1
server-id =2
#server-id = 2
#log-bin=mysql-bin
[root@slave /]#
3）主服务器操作
3.1）首先登陆数据库，创建macc（master salve connect common）主从复制专用账户，
mysql> grant replication slave on *.* to 'macc'@'192.168.32.%' identified by 'nick'; #创建macc账户并授权192.168.32的网段可以连接，密码为nick
Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.02 sec) mysql> select user,host from mysql.user; 查看macc用户
+------+--------------+
| user | host |
+------+--------------+
| root | 127.0.0.1 |
| macc | 192.168.32.% |
| root | ::1 |
| | localhost |
| root | localhost |
| | slave |
| root | slave |
+------+--------------+
7 rows in set (0.00 sec) mysql>
3.2)然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作
mysql> flush tables with read lock; #锁表
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; #获取二进制日志信息我的可以到/home/software/mysql-5.5.32/data/mysql-bin. 找到，默认都是在data目录下。
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |#flie的值是当前二进制日志的文件名，position是该日志的位置信息，会在配置从时候用到
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 471 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) mysql>
3.3)让大家看看我的二进制日志位置（这个不是必要操作）
[root@slave /]# cd /home/software/mysql-5.5.32/data/
[root@slave data]# ls
ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql-bin.index slave.err test
ib_logfile0 mysql mysql-bin.000002 mysql-bin.000004 performance_schema slave.pid
[root@slave data]#
注意:如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句show master status会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）
3.4）创建mysql备份目录backup，并开始备份数据，mysqldump备份命令请点击（前面是源码，后面是网上源码总结的）
[root@master software]# mkdir backup #创建mysql备份的目录
[root@master software]# ls
backup cmake-2.8.8 cmake-2.8.8.tar.gz mysql mysql-5.5.32 mysql-5.5.32.tar.gz
[root@master software]# cd /home/software/mysql-5.5.32/sock/ #因为用的mysqldump备份，需要指定sock，这就是cmake编译时候指定的路径
[root@master sock]# ls
mysql.sock
[root@master sock]#
[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
-bash: date+%F: command not found # 这里报错是因为$(date +%F)需要有空格，且前面我没加点导致的
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[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
Enter password:
[root@master sock]# cd /home/software/backup/
[root@master backup]# ls
mysql_macc_bak.2019-06-01.sql.gz mysql_macc_bak.sql.gz
[root@master backup]# ls
mysql_macc_bak.2019-06-01.sql.gz mysql_macc_bak.sql.gz
3.5)这个时候就可以解锁表了
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
四）从服务器配置
4.1)查看从服务器的sesrver-id
[root@slave /]# egrep "log-bin|server-id" /etc/my.cnf
log-bin=mysql-bin
#server-id = 1
server-id =2
#server-id = 2
#log-bin=mysql-bin
[root@slave /]#
注意：从服务器上没必要打开log-bin，当然也可以打开log-bin选项，这样可以在从服务器上进行数据备份和灾难恢复，或者让这个从服务器变成一个主服务器。
mysql> select user,host from mysql.user; #查看从库的用户macc
+------+--------------+
| user | host |
+------+--------------+
| root | 127.0.0.1 |
| macc | 192.168.32.% |
| root | ::1 |
| | localhost |
| root | localhost |
| | slave |
| root | slave |
+------+--------------+
7 rows in set (0.00 sec) mysql> change master to #参数配置见下面的详细解释
-> MASTER_HOST='192.168.32.132',
-> MASTER_PORT=3306,
-> MASTER_USER='macc',
-> MASTER_PASSWORD='nick',
-> MASTER_LOG_FILE='mysql-bin.000004',
-> MASTER_LOG_POS=245;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave
-> ;
Query OK, 0 rows affected (0.02 sec) mysql> change master to
-> MASTER_HOST='192.168.32.132',
-> MASTER_PORT=3306,
-> MASTER_USER='macc',
-> MASTER_PASSWORD='nick',
-> MASTER_LOG_FILE='mysql-bin.000004',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.02 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: 192.168.32.132
Master_User: macc
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes #io进程负责从库去主库读取二进制日志，并写到从库的中继日志中，io跟sql进程出现no，从服务器要跟主服务器通，server-id要不一样
Slave_SQL_Running: Yes #sql进程负责将中继日志换成sql语句后执行
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: 107
Relay_Log_Space: 856
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
1 row in set (0.00 sec) mysql>
4.2)验证主从是否完成
在主库上创建master数据库，到从库查看，
[root@master backup]# mysql -uroot -p #这个是主库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec) mysql> create database master;
Query OK, 1 row affected (0.01 sec) mysql>
4.3）从库查看
[root@slave software]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec) mysql> show databases; #可以看到在mysql主上创建的master数据库有了，
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec) mysql>
4.4)到此mysql主从完成，在mysql从服务器上做一个备份（这边我没有关闭slave从服务器，应该先关闭从服务器备份，在启动，这样保证备份数据的完整性。）
[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
Enter password:
[root@slave software]# cd backup/
[root@slave backup]# ls
mysql_macc_bak.2019-06-01.sql.gz
[root@slave backup]#
4）Shell脚本监控mysql主从
#!/bin/bash
#Date:--
#Author:king
#version 1.0
mysql_cmd="mysql -u root -proot"
errorno=( )
while true
do
array=($($mysql_cmd -e "show slave status\G"|egrep '_Running|Behind_Master|Last_SQL_Errno'|awk '{print $NF}'))
if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "" ]
then
echo "MySQL is slave is ok"
else
for ((i=;i<${#errorno[*]};i++))
do
if [ "${array[3]}" = "${errorno[$i]}" ];then
$mysql_cmd -e "stop slave &&set global sql_slave_skip_counter=1;start slave;"
fi
done
char="MySQL slave is not ok"
echo "$char"
echo "$char"|mail -s "$char" 838915***@qq.com
break
fi
sleep
done
4.1）执行结果
[root@slave_lamp ~]# sh test.sh
MySQL is slave is ok
MySQL is slave is ok
次脚本可用于生产案例，如果测试没有收到邮件，不要谎，这个是vps禁用了 25端口问题，欢迎大家留意帮忙解决哈，当然第三方就算了
五）留记录，yum安装mysql5.7
rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm #添加mysql yum源
yum repolist all | grep mysql #查看yum源中所有Mysql版本
yum-config-manager --disable mysql80-community #此时的最新版本是mysql8.，把它禁用掉
yum-config-manager --enable mysql57-community #mysql5.7是我要安装的版本，启用mysql5.
yum repolist enabled | grep mysql #检查刚才的配置是否生效
yum install mysql-community-server -y #开始安装
systemctl start mysqld.service #启动mysql服务
systemctl status mysqld #检查mysql状态，
grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; #登录mysql后必须修改密码，密码简单会报错ERROR (HY000): Your password does not satisfy the current policy requirements
set password for root@localhost = password('root'); #这是改mysql密码的passwor函数，修改mysql密码很多种，选一种就可以了
set global validate_password_policy=; #修改validate_password_policy参数的值
set global validate_password_length=; #再修改密码的长度，然后在执行修改密码就好了
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; #授权其他机器可以登录
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; #为mysql，root用户增加远程连接能力