Yum编译安装MySQL,MySQL数据库基于Centos7【澳门金莎

日期:2019-10-26编辑作者:数据大全

一、MySQL数据库的官方网址:

  • https://www.oracle.com/
  • http://dev.mysql.com/doc/refman/5.7/en/linux-installation.html
  •        //不同版本的安装文档和下载可以在这里找

1.1 rpm包安装mysql

直接yum安装mysql-server即可。但注意两点:

(1).centos7上,yum默认将安装mariadb。
(2).centos6上,yum默认安装的版本比较老(5.1版),要安装mysql 5.6或mysql 5.7,可以从官方下载,也可以使用以下配置的yum源。

release=`awk  -F "[ .]" '{print $4}' /etc/redhat-release`
cat <<eof>/etc/yum.repos.d/mysql.repo
[mysql56]
name=MySQL
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/$release/$basearch/
enabled=1
gpgcheck=0

[mysql57]
name=MySQL
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/$release/$basearch/
enabled=1
gpgcheck=0
eof

此外,在sohu镜像站点也提供了mysql的各个rpm版本。地址:http://mirrors.sohu.com/mysql/

还需注意,配置了yum源后安装Mysql将使用mysql-community-*安装各mysql相关包,例如mysql-community-server。

安装完成后,启动mysqld。

shell> service mysqld start      # 或 systemctl start mysqld

如果启动失败,则可能需要初始化MySQL。

shell> mkdir -p /mydata/data
shell> chown -R mysql.mysql /mydata/data
shell> mysql_install_db --datadir=/mydata/data --user=mysql

如果使用mysql_install_db初始化时提示该命令已经废弃(5.7版本可能会如此提示),那么使用下面的命令进行初始化。

# 初始化时,为root@localhost创建一个临时密码存放在mysql.log中
shell> mysqld --initialize --datadir=/mydata/data --user=mysql
# 初始化时,为root@localhost创建一个空密码
shell> mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

初始化后再启动,启动成功后连接数据库并修改root@localhost用户的密码,然后退出。

shell> mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> q

如果前面使用mysqld --initialize初始化数据库,那么将会为"root@localhost"创建一个密码,这将使得无法直接使用mysql命令连接数据库。可以先从mysql.log中筛选出创建的临时密码,然后再手动修改为"123456"。

shell> grep 'temporary password' /var/log/mysqld.log
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> q

1.3.1 编译安装过程

关于编译选项,见下文。

使用以下命令安装mysql 5.6。

yum -y install ncurses-devel cmake
tar xf ~/mysql-5.6.37.tar.gz
cd ~/mysql-5.6.37
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.37 
-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 
-DWITH_FAST_MUTEXES=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_SSL=bundled 
-DWITH_DEBUG=0 
make
make install

使用以下命令安装mysql 5.7。比上述多了最后一个boost相关设置项,不设置此项可能会cmake失败。

yum -y install ncurses-devel cmake
tar xf ~/mysql-5.7.19.tar.gz
cd ~/mysql-5.7.19
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.19 
-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 
-DWITH_FAST_MUTEXES=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_SSL=bundled 
-DWITH_DEBUG=0 
-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
make
make install

如果是centos7,则加上一项-DWITH_SYSTEMD=1,这会提供systemd脚本。

注意,上述编译选项中没有提供sysconfdir、datadir、pidfile和socket等mysql运行时文件类设置选项。虽然可以设置,但没必要,而且在做多实例的时候可能会出现问题。不过,可以考虑加上MYSQL_UNIX_ADDR项来设置socket路径,因为不设置的话其会采用默认的/tmp/mysql.sock。

您可能感兴趣的文章:

  • Centos7下使用yum安装mysql数据库的详细教程(增强版)
  • centos7 用rpm安装mysql详解
  • CentOS7.2虚拟机上安装MySQL 5.6.32的教程
  • CentOS 7 x64下Apache+MySQL(Mariadb)+PHP56的安装教程详解
  • centos 7系统下编译安装 mysql5.7教程
  • CentOS 7 安装 MySQL 5.6遇到的各种问题小结
  • centOS7安装MySQL数据库
  • Centos 6.4源码安装mysql-5.6.28.tar.gz教程
  • linux下源码安装mysql5.6.20教程
  • CentOS 6.4下编译安装MySQL5.6.14教程

可参考:【MySQL解惑笔记】Centos7下卸载彻底MySQL数据库

卸载maridb:
[root@host-131 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@host-131 ~]# yum -y remove mari*
[root@host-131 ~]# rm -rf /var/lib/mysql/*
[root@host-131 ~]# rpm -qa | grep mariadb
[root@host-131 ~]# 

4、修改仓库

[root@host-131 src]# yum rpolist all |grep mysql      //查看所有仓库,并能够看到开启和禁用的仓库
[root@host-131 src]# yum repolist enabled | grep "mysql.*-community.*"  //查看所有开启的仓库,发现现在开启的是MySQL 8.0的,我们需要开启MySQL5.7的仓库
mysql-connectors-community/x86_64 MySQL Connectors Community                  51
mysql-tools-community/x86_64      MySQL Tools Community                       63
mysql80-community/x86_64          MySQL 8.0 Community Server                  17     //k

 开启和关闭不同仓库,官方提供了三种修改方法:

方法一:
shell> sudo yum-config-manager --disable mysql80-community
shell> sudo yum-config-manager --enable mysql57-community

方法二:
shell> sudo dnf config-manager --disable mysql57-community
shell> sudo dnf config-manager --enable mysql56-community

方法三:
我们直接修改Yum源文件:
[root@host-131 ~]# cd /etc/yum.repos.d 
[root@host-131 yum.repos.d]# vim mysql-community.repo 
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1                        //修改为1表示开启
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0                        //修改为0表示关闭
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[root@host-131 yum.repos.d]# yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64 MySQL Connectors Community                  51
mysql-tools-community/x86_64      MySQL Tools Community                       63
mysql57-community/x86_64          MySQL 5.7 Community Server                 267    //可以看到5.7存储仓库已经开启

注意:我们应该在任何时候只启用一个发布系列的子存储库。当启用多个发布系列的子存储库时,Yum将使用最新的系列。
通过运行以下命令并检查其输出(对于Fedora,用dnf替换yum),验证是否启用了正确的子库并禁用了它:
[root@host-131 yum.repos.d]# yum repolist enabled | grep mysql
mysql-connectors-community/x86_64 MySQL Connectors Community                  51
mysql-tools-community/x86_64      MySQL Tools Community                       63
mysql57-community/x86_64          MySQL 5.7 Community Server                 267

 5、安装MySQL数据库

[root@host-131 yum.repos.d]# yum -y install mysql-community-server
[root@host-131 ~]# systemctl start mysqld   
[root@host-131 ~]# systemctl enable mysqld   设置开机自启动
[root@host-131 ~]# systemctl status mysqld  查看mysql启动状态

[root@host-131 ~]# ls /var/lib/mysql    (安装后可以看到初始化的数据库)
auto.cnf    client-cert.pem  ibdata1      ibtmp1      mysql.sock.lock     public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  mysql   performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  mysql.sock  private_key.pem     server-key.pem

6、首次登录数据库,并设置初始化密码

[root@host-131 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
发现是禁止访问的

在服务器的初始启动时,由于服务器的数据目录为空:服务器初始化。SSL证书和密钥文件是在数据目录中生成的。
安装并启用了validate_password。
一个超级用户帐户'root'@'localhost被创建。
超级用户的密码被设置并存储在错误日志文件中。要显示它,请使用以下命令:

MySQL5.5之前版本安装后可以直接登录
MySQL5.5自动生成的密码是空的
MySQL5.6是将密码放在root下的
MySQL5.7是将密码放在:
[root@host-131 ~]# grep 'temporary password' /var/log/mysqld.log
2018-05-30T06:34:56.956716Z 1 [Note] A temporary password is generated for root@localhost: xt8S3rWXOL/C
[root@host-131 ~]# 
[root@host-131 ~]# mysql -uroot -p"xt8S3rWXOL/C"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.22
修改密码:
方法一:
[root@host-131 ~]# mysqladmin -uroot -p"xt8S3rWXOL/C" password "Yanglt123."   //密码要满足复杂性要求
[root@host-131 ~]# mysql -uroot -p"Yanglt123."

方法二:

[root@host-131 ~]# grep 'temporary password' /var/log/mysqld.log
2018-05-30T06:34:56.956716Z 1 [Note] A temporary password is generated for root@localhost: xt8S3rWXOL/C
[root@host-131 ~]# 
[root@host-131 ~]# mysql -uroot -p"xt8S3rWXOL/C"

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

 到此数据库安装成功:

源码安装可参考文章:*【Linux运维】LNMP环境配置*

  

Storage Engine Options

存储引擎是插件式的,可被静态编译到MySQL服务中,也可以动态编译成模块,编译成模块时需要使用INSTALL PLUGIN语句或者--plugin-load选项来启用。但某些插件是固化的,无法指定是静态编译还是动态编译。

InnoDB,MyISAM,MERGE,MEMORY和CSV存储引擎总是默认静态编译到MySQL服务中的,在编译安装的时候无需显式指定它们。

编译存储引擎的时候,使用-DWITH_enginename_STORAGE_ENGINE=1表示静态编译到MySQL。可选的引擎有:ARCHIVE、BLACKHOLE、EXAMPLE、FEDERATED、PARTITION(分区支持引擎)、PERFSCHEMA(Performance Schema)。如:

-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

使用-WITHOUT_enginename_STORAGE_ENGINE=1表示显式的排除这些引擎,即强制不使用它们。如:

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_enginename_STORAGE_ENGINE-WITHOUT_enginename_STORAGE_ENGINE都没有指定某些引擎的话,这些引擎默认动态编译成模块,如果无法动态编译成模块则不编译。

注:若您觉得这篇文章还不错请点击右下角推荐,您的支持能激发作者更大的写作热情,非常感谢!

[root@linuxprobe ~]# firewall-cmd –add-service=MySQL –permanent 
success 
[root@linuxprobe ~]# firewall-cmd –reload 
success

三、Yum的方法安装MySQL:

  • yum安装mysql 5.7 的官方安装方法地址

1、下载安装包

  • *此地址为Mysql 8.0 的安装源其中包含MySQL 5.7的源,可以在后边步骤修改开放的仓库: *

2、 设置安装初始环境

暂时先把防火墙关闭保证实验顺利:
[root@host-131 ~]# systemctl stop firewalld 
[root@host-131 ~]# systemctl disable firewalld.service
临时关闭SELlinux:
[root@host-131 ~]# setenforce 0
永久关闭SELinux:
[root@host-131 ~]# sed -ri '/^SELINUX=/cSELINUX=disabled' /etc/selinux/config
查看SELinux状态:
[root@host-131 ~]# getenforce
Disabled

 3、卸载系统自带的数据库(Centos7自带的是maridb)

2.3 提供systemd服务管理脚本

  • 以下是3306实例的管理脚本/usr/lib/systemd/system/mysqld3306.service。
  • 由于我的示例中mysql是采用rpm安装,所以mysql的basedir为/usr,如果是编译安装或通用二进制安装,则对应修改下面脚本中的basedir变量。
  • 由于rpm包安装在centos 7上,已经没有mysqld_safe命令,因此使用mysqld来启动mysql实例。
  • 将此管理脚本复制为/usr/lib/systemd/system/mysqld3307.service,再将其内的3306修改为3307即可作为3307实例的服务管理脚本。
  • 此脚本不会初始化mysql(我把这部分代码删了)创建实例,所以启动服务前务必先初始化好对应的mysql实例。
  • 下面的脚本即可作为多实例服务管理脚本,也可以作为单实例服务管理脚本,只需将脚本名称改一改即可。
  • mysql为systemd自带了多实例服务管理脚本/usr/lib/systemd/system/mysqld@.service。我没有采用,但其设置方法可以借鉴下。
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/mydata/3306/data/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Start main service
ExecStart=/usr/sbin/mysqld --defaults-file=/mydata/3306/my.cnf --basedir=/usr --daemonize $MYSQLD_OPTS
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

然后重载下systemd服务管理脚本。

systemctl daemon-reload

之后就可以使用下面的命令管理两个实例:

systemctl {start|stop|status|restart} mysqld3306
systemctl {start|stop|status|restart} mysqld3307

1.3.3 规范化

输出头文件、库文件,设置PATH环境变量,设置man路径。

echo "export PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
chmod +x /etc/profile.d/mysql.sh
echo "MANPATH /usr/local/mysql/man" >>/etc/man.config
echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig
ln -s /usr/local/mysql/include /usr/include/mysql
[root@vdevops ~]# mysql -u root -p < /tmp/mysql_dump.sql
Enter password: 
[root@vdevops ~]# 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.6.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> change master to
 -> master_host='10.1.1.53', # master ip
 -> master_user='replica', # replication ID
 -> master_password='password', # replication passwd
 -> master_log_file='mysql-bin.000001', # bin-log number
 -> master_log_pos=400;  # position
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave statusG
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
   Master_Host: 10.1.1.53
   Master_User: replica
   Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000001
  Read_Master_Log_Pos: 475
  Relay_Log_File: mysqld-relay-bin.000002
  Relay_Log_Pos: 358
 Relay_Master_Log_File: mysql-bin.000001
  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: 475
  Relay_Log_Space: 532
  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: 101
   Master_UUID: d47dddc3-9c70-11e6-8efb-000c29ffc05a
  Master_Info_File: /var/opt/rh/rh-mysql56/lib/mysql/master.info
   SQL_Delay: 0
  SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  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
1 row in set (0.00 sec)

二、MySQL的安装方法:

以下是MySQL常见的三种安装方式:

  • 二进制  rpm   Yum Repository    mysql57-community-release-el7-9.noarch.rpm
  • 二进制  预编译  Generic                        mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
  • 源码包安             Source Code                mysql-5.7.19.tar.gz

3. mariadb安装

mariadb基本上算是mysql的另一个实现,绝大多数以及基础功能上和MySQL都相同。具体到安装上,也基本完全一样。

在centos7上,直接yum install mysql-server将默认安装mariadb,如果配置了mysql的yum源,需要指定"mysql-community-server"才表示安装mysql。

以下仅提供mariadb的各项资源下载地址,具体安装方法见前文对应mysql安装方法。个人建议,将mariadb的服务启动脚本阅读一遍,和MySQL的做个比较。

mariadb的镜像站点:

[mariadb]
name=mariadb
baseurl=http://yum.mariadb.org/10.2.6/centos/6.6/$basearch/
#baseurl=http://yum.mariadb.org/10.2.6/centos/7.2/$basearch/
enabled=1
gpgcheck=0

mariadb各通用二进制版:

mariadb各种二进制版本:https://downloads.mariadb.org/mariadb/+releases/

mariadb 10.2.6 systemd版(centos7):http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/bintar-linux-systemd-x86_64/mariadb-10.2.6-linux-systemd-x86_64.tar.gz

mariadb 10.2.6 非systemd版(centos6):http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/bintar-linux-x86_64/mariadb-10.2.6-linux-x86_64.tar.gz

mariadb源码包: http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/source/mariadb-10.2.6.tar.gz

2. mysql多实例配置

mysql可以实现多实例,但因为多实例会共用服务器资源,导致资源争用,在某实例某一刻资源占用很多时(高并发、慢查询),其他的实例会受到影响。

无论是rpm安装、通用二进制安装还是编译安装,都有两种方法实现多实例。

  1. 共用配置文件
    在my.cnf中配置多个"[mysqldN]",N是一个数字,表示MySQL服务。
    启停的时候使用mysqld_multi {start|stop|restart} N。N可以是单个数字,也可以是逗号分隔的多个数字,还可以是短横线表示的范围数字。如果不是rpm包安装的,则mysqld_multi文件的路径在support-files中,将其copy到/etc/init.d下即可(没有原生态的systemd多实例服务管理脚本)。

    mysqld_multi start 1,2,4-6
    
  2. 单独的配置文件和启动程序(推荐)
    MySQL只需安装一次,即不同实例使用同一安装程序。但每个实例使用单独的配置文件、服务管理脚本、datadir目录和socket,并且启停mysqld服务时需要指定套接字文件。

[3] 添加 CentOS SCLo Software collections Repository.
[root@linuxprobe~]# yum -y install centos-release-scl-rh centos-release-scl
# set [priority=10]
[root@linuxprobe~]# sed -i -e "s/]$/]npriority=10/g" /etc/yum.repos.d/CentOS-SCLo-scl.repo
[root@linuxprobe~]# sed -i -e "s/]$/]npriority=10/g" /etc/yum.repos.d/CentOS-SCLo-scl-rh.repo
# for another way, change to [enabled=0] and use it only when needed
[root@linuxprobe~]# sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/CentOS-SCLo-scl.repo
[root@dlp ~]# sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/CentOS-SCLo-scl-rh.repo
  1. 本文的安装环境为centos6.6和centos7.2,但大多数地方都以centos6.6作为演示示例。
  2. 本文安装MySQL时,它们的运行身份为mysql,数据目录datadir为/mydata/data。
  3. pid文件路径设置为/mydata/data/mysql.pid或/mydata/data/`hostname`.pid。
  4. 由于mariadb和mysql 5系列并没有太大的不同。因此仅详细展示mysql的安装。最后将简单提一提mariadb。

  5. mysql单实例安装

回到数据库系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7586194.html

开启防火墙

2.2 提供sysV服务管理脚本

再分别提供服务管理脚本。

  • 以下是3306实例的管理脚本/etc/init.d/mysqld3306,内容修改自原有管理脚本/etc/init.d/mysqld。
  • 由于我的示例中mysql是采用rpm安装,所以mysql的basedir为/usr,如果是编译安装或通用二进制安装,则对应修改下面脚本中的basedir变量。
  • 下面的datadir变量设置为/mydata/$port/data。请务必和上面的初始化设置和配置文件中设置的相同。
  • 将此管理脚本复制为/etc/init.d/mysqld3307,再修改下port=3307即可作为3307实例的服务管理脚本。
  • 此脚本不会初始化mysql(我把这部分代码删了)创建实例,所以启动服务前务必先初始化好对应的mysql实例。
  • 下面的脚本即可作为多实例服务管理脚本,也可以作为单实例服务管理脚本,只需将脚本名称改一改即可。
#!/bin/sh
#
# mysqld    This shell script takes care of starting and stopping
#        the MySQL subsystem (mysqld).
#
# chkconfig: 345 64 36
# description:    MySQL database server.
# processname: mysqld

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

basedir=/usr
exec="$basedir/bin/mysqld_safe"
prog="mysqld"
port=3306
datadir="/mydata/$port/data"
socketfile="$datadir/mysql.sock"
errlogfile="$datadir/mysqld.log"
mypidfile="$datadir/mysqld.pid"
cnf="/mydata/$port/my.cnf"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

# Set in /etc/sysconfig/mysqld, will be passed to mysqld_safe
MYSQLD_OPTS=

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog

case $socketfile in
    /*) adminsocket="$socketfile" ;;
     *) adminsocket="$datadir/$socketfile" ;;
esac

start(){
    [ -x $exec ] || exit 5
    # check to see if it's already running
    RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1)
    if [ $? = 0 ]; then
    # already running, do nothing
    action $"Starting $prog: " /bin/true
    ret=0
    elif echo "$RESPONSE" | grep -q "Access denied for user"
    then
    # already running, do nothing
    action $"Starting $prog: " /bin/true
    ret=0
    else
    # Now start service
    $exec $MYSQLD_OPTS --defaults-file="$cnf" --datadir="$datadir" --socket="$socketfile" 
        --pid-file="$mypidfile" 
        --basedir="$basedir" --user=mysql >/dev/null &
    safe_pid=$!
    # Spin for a maximum of N seconds waiting for the server to come up;
    # exit the loop immediately if mysqld_safe process disappears.
    # Rather than assuming we know a valid username, accept an "access
    # denied" response as meaning the server is functioning.
    ret=0
    TIMEOUT="$STARTTIMEOUT"
    while [ $TIMEOUT -gt 0 ]; do
        RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1) && break
        echo "$RESPONSE" | grep -q "Access denied for user" && break
        if ! /bin/kill -0 $safe_pid 2>/dev/null; then
        echo "MySQL Daemon failed to start."
        ret=1
        break
        fi
        sleep 1
        let TIMEOUT=${TIMEOUT}-1
    done
    if [ $TIMEOUT -eq 0 ]; then
        echo "Timeout error occurred trying to start MySQL Daemon."
        ret=1
    fi
    if [ $ret -eq 0 ]; then
        action $"Starting $prog: " /bin/true
        touch $lockfile
    else
        action $"Starting $prog: " /bin/false
    fi
    fi
    return $ret
}

stop(){
    if [ ! -f "$mypidfile" ]; then
        # not running; per LSB standards this is "ok"
        action $"Stopping $prog: " /bin/true
        return 0
    fi
    MYSQLPID=`cat "$mypidfile"`
    if [ -n "$MYSQLPID" ]; then
        /bin/kill "$MYSQLPID" >/dev/null 2>&1
        ret=$?
        if [ $ret -eq 0 ]; then
        TIMEOUT="$STOPTIMEOUT"
        while [ $TIMEOUT -gt 0 ]; do
            /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
            sleep 1
            let TIMEOUT=${TIMEOUT}-1
        done
        if [ $TIMEOUT -eq 0 ]; then
            echo "Timeout error occurred trying to stop MySQL Daemon."
            ret=1
            action $"Stopping $prog: " /bin/false
        else
            rm -f $lockfile
            rm -f "$socketfile"
            action $"Stopping $prog: " /bin/true
        fi
        else
        action $"Stopping $prog: " /bin/false
        fi
    else
        # failed to read pidfile, probably insufficient permissions
        action $"Stopping $prog: " /bin/false
        ret=4
    fi
    return $ret
}

restart(){
    stop
    start
}

condrestart(){
    [ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  status)
    status -p "$mypidfile" $prog
    ;;
  restart)
    restart
    ;;
  condrestart|try-restart)
    condrestart
    ;;
  reload)
    exit 3
    ;;
  force-reload)
    restart
    ;;
  *)
    echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
    exit 2
esac

exit $?

使用下面的命令管理两个实例:

service mysqld3306 {start|stop|status|restart}
service mysqld3307 {start|stop|status|restart}

第一次启动可能会失败,也可能会警告和log相关的项,这是正常的,之后都会正常。

本文介绍mysql各种安装方法(rpm/glibc通用二进制/源码编译)以及多实例配置的方法,没什么技术,算是一篇方法归总文章。

同样使用yum在vdevops.org上面安装mysql,安装步骤参考上面

1.2.2 安装后的规范化操作

编译安装或通用二进制安装后,一般都需要做一些额外的操作,包括设置环境变量、输出头文件和库文件、设置man路径。

echo "export PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
chmod +x /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
echo "MANPATH /usr/local/mysql/man" >>/etc/man.config

echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig
ln -s /usr/local/mysql/include /usr/include/mysql

1.3.4 cmake编译MySQL时的选项说明

精彩专题分享:mysql不同版本安装教程 mysql5.7各版本安装教程 mysql5.6各版本安装教程

1.3.1 编译安装过程

关于编译选项,见下文。

使用以下命令安装mysql 5.6。

yum -y install ncurses-devel cmake
tar xf ~/mysql-5.6.37.tar.gz
cd ~/mysql-5.6.37
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.37 
-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 
-DWITH_FAST_MUTEXES=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_SSL=bundled 
-DWITH_DEBUG=0 
make
make install

使用以下命令安装mysql 5.7。比上述多了最后一个boost相关设置项,不设置此项可能会cmake失败。

yum -y install ncurses-devel cmake
tar xf ~/mysql-5.7.19.tar.gz
cd ~/mysql-5.7.19
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.19 
-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 
-DWITH_FAST_MUTEXES=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_SSL=bundled 
-DWITH_DEBUG=0 
-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
make
make install

如果是centos7,则加上一项-DWITH_SYSTEMD=1,这会提供systemd脚本。

注意,上述编译选项中没有提供sysconfdir、datadir、pidfile和socket等mysql运行时文件类设置选项。虽然可以设置,但没必要,而且在做多实例的时候可能会出现问题。不过,可以考虑加上MYSQL_UNIX_ADDR项来设置socket路径,因为不设置的话其会采用默认的/tmp/mysql.sock。

回到Linux系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7048359.html

[root@vdevops~]# vi /etc/opt/rh/rh-mysql56/my.cnf.d/mysql-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# define server ID (different one from Master Host)
server-id=102
# read only
read_only=1
# define own hostname
report-host=node01.srv.world
[root@vdevops~]# systemctl restart rh-mysql56-mysqld

1.3.3 规范化

输出头文件、库文件,设置PATH环境变量,设置man路径。

echo "export PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
chmod +x /etc/profile.d/mysql.sh
echo "MANPATH /usr/local/mysql/man" >>/etc/man.config
echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig
ln -s /usr/local/mysql/include /usr/include/mysql

2.2 提供sysV服务管理脚本

再分别提供服务管理脚本。

  • 以下是3306实例的管理脚本/etc/init.d/mysqld3306,内容修改自原有管理脚本/etc/init.d/mysqld。
  • 由于我的示例中mysql是采用rpm安装,所以mysql的basedir为/usr,如果是编译安装或通用二进制安装,则对应修改下面脚本中的basedir变量。
  • 下面的datadir变量设置为/mydata/$port/data。请务必和上面的初始化设置和配置文件中设置的相同。
  • 将此管理脚本复制为/etc/init.d/mysqld3307,再修改下port=3307即可作为3307实例的服务管理脚本。
  • 此脚本不会初始化mysql(我把这部分代码删了)创建实例,所以启动服务前务必先初始化好对应的mysql实例。
  • 下面的脚本即可作为多实例服务管理脚本,也可以作为单实例服务管理脚本,只需将脚本名称改一改即可。
#!/bin/sh
#
# mysqld    This shell script takes care of starting and stopping
#        the MySQL subsystem (mysqld).
#
# chkconfig: 345 64 36
# description:    MySQL database server.
# processname: mysqld

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

basedir=/usr
exec="$basedir/bin/mysqld_safe"
prog="mysqld"
port=3306
datadir="/mydata/$port/data"
socketfile="$datadir/mysql.sock"
errlogfile="$datadir/mysqld.log"
mypidfile="$datadir/mysqld.pid"
cnf="/mydata/$port/my.cnf"

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60

# Set in /etc/sysconfig/mysqld, will be passed to mysqld_safe
MYSQLD_OPTS=

[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog

lockfile=/var/lock/subsys/$prog

case $socketfile in
    /*) adminsocket="$socketfile" ;;
     *) adminsocket="$datadir/$socketfile" ;;
esac

start(){
    [ -x $exec ] || exit 5
    # check to see if it's already running
    RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1)
    if [ $? = 0 ]; then
    # already running, do nothing
    action $"Starting $prog: " /bin/true
    ret=0
    elif echo "$RESPONSE" | grep -q "Access denied for user"
    then
    # already running, do nothing
    action $"Starting $prog: " /bin/true
    ret=0
    else
    # Now start service
    $exec $MYSQLD_OPTS --defaults-file="$cnf" --datadir="$datadir" --socket="$socketfile" 
        --pid-file="$mypidfile" 
        --basedir="$basedir" --user=mysql >/dev/null &
    safe_pid=$!
    # Spin for a maximum of N seconds waiting for the server to come up;
    # exit the loop immediately if mysqld_safe process disappears.
    # Rather than assuming we know a valid username, accept an "access
    # denied" response as meaning the server is functioning.
    ret=0
    TIMEOUT="$STARTTIMEOUT"
    while [ $TIMEOUT -gt 0 ]; do
        RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1) && break
        echo "$RESPONSE" | grep -q "Access denied for user" && break
        if ! /bin/kill -0 $safe_pid 2>/dev/null; then
        echo "MySQL Daemon failed to start."
        ret=1
        break
        fi
        sleep 1
        let TIMEOUT=${TIMEOUT}-1
    done
    if [ $TIMEOUT -eq 0 ]; then
        echo "Timeout error occurred trying to start MySQL Daemon."
        ret=1
    fi
    if [ $ret -eq 0 ]; then
        action $"Starting $prog: " /bin/true
        touch $lockfile
    else
        action $"Starting $prog: " /bin/false
    fi
    fi
    return $ret
}

stop(){
    if [ ! -f "$mypidfile" ]; then
        # not running; per LSB standards this is "ok"
        action $"Stopping $prog: " /bin/true
        return 0
    fi
    MYSQLPID=`cat "$mypidfile"`
    if [ -n "$MYSQLPID" ]; then
        /bin/kill "$MYSQLPID" >/dev/null 2>&1
        ret=$?
        if [ $ret -eq 0 ]; then
        TIMEOUT="$STOPTIMEOUT"
        while [ $TIMEOUT -gt 0 ]; do
            /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
            sleep 1
            let TIMEOUT=${TIMEOUT}-1
        done
        if [ $TIMEOUT -eq 0 ]; then
            echo "Timeout error occurred trying to stop MySQL Daemon."
            ret=1
            action $"Stopping $prog: " /bin/false
        else
            rm -f $lockfile
            rm -f "$socketfile"
            action $"Stopping $prog: " /bin/true
        fi
        else
        action $"Stopping $prog: " /bin/false
        fi
    else
        # failed to read pidfile, probably insufficient permissions
        action $"Stopping $prog: " /bin/false
        ret=4
    fi
    return $ret
}

restart(){
    stop
    start
}

condrestart(){
    [ -e $lockfile ] && restart || :
}


# See how we were called.
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  status)
    status -p "$mypidfile" $prog
    ;;
  restart)
    restart
    ;;
  condrestart|try-restart)
    condrestart
    ;;
  reload)
    exit 3
    ;;
  force-reload)
    restart
    ;;
  *)
    echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload}"
    exit 2
esac

exit $?

使用下面的命令管理两个实例:

service mysqld3306 {start|stop|status|restart}
service mysqld3307 {start|stop|status|restart}

第一次启动可能会失败,也可能会警告和log相关的项,这是正常的,之后都会正常。

Master端:备份数据库

2. mysql多实例配置

mysql可以实现多实例,但因为多实例会共用服务器资源,导致资源争用,在某实例某一刻资源占用很多时(高并发、慢查询),其他的实例会受到影响。

无论是rpm安装、通用二进制安装还是编译安装,都有两种方法实现多实例。

  1. 共用配置文件
    在my.cnf中配置多个"[mysqldN]",N是一个数字,表示MySQL服务。
    启停的时候使用mysqld_multi {start|stop|restart} N。N可以是单个数字,也可以是逗号分隔的多个数字,还可以是短横线表示的范围数字。如果不是rpm包安装的,则mysqld_multi文件的路径在support-files中,将其copy到/etc/init.d下即可(没有原生态的systemd多实例服务管理脚本)。

    mysqld_multi start 1,2,4-6
    
  2. 单独的配置文件和启动程序(推荐)
    MySQL只需安装一次,即不同实例使用同一安装程序。但每个实例使用单独的配置文件、服务管理脚本、datadir目录和socket,并且启停mysqld服务时需要指定套接字文件。

Installation Layout Options

  • -DCMAKE_INSTALL_PREFIX=dir_name   # MySQL的安装位置
  • -DINSTALL_PLUGINDIR=dir_name      # 插件安装的目录
  • -DMYSQL_DATADIR=dir_name        # MySQL的data dir
  • -DSYSCONFDIR=dir_name        # MySQL默认的配置文件(my.cnf)路径
  • -DTMPDIR=dir_name          # 临时文件存放路径,在MySQL5.6.16中才开始提供该选项

MySQL 主从

本文介绍mysql各种安装方法(rpm/glibc通用二进制/源码编译)以及多实例配置的方法,没什么技术,算是一篇方法归总文章。

  1. 本文的安装环境为centos6.6和centos7.2,但大多数地方都以centos6.6作为演示示例。
  2. 本文安装MySQL时,它们的运行身份为mysql,数据目录datadir为/mydata/data。
  3. pid文件路径设置为/mydata/data/mysql.pid或/mydata/data/`hostname`.pid。
  4. 由于mariadb和mysql 5系列并没有太大的不同。因此仅详细展示mysql的安装。最后将简单提一提mariadb。

  5. mysql单实例安装

Slave端:vdevops

1.3.4 cmake编译MySQL时的选项说明

1.2.1 初始化数据库

不使用rpm包安装,就需要对mysql进行初始化以创建一些文件、库和指定一些参数。但在初始化mysql前,要预先做一些操作。

shell> mkdir -p /mydata/data
shell> useradd -r -s /sbin/nologin mysql
shell> chown -R mysql.mysql /usr/local/mysql
shell> chown -R mysql.mysql /mydata/data
shell> cd /usr/local/mysql
shell> scripts/mysql_install_db --datadir=/mydata/data --user=mysql
shell> chown -R root.root /usr/local/mysql

执行mysql_install_db时会在/tmp下创建临时表,所以mysql用户需要对/tmp有写权限,否则执行实例初始化脚本时可能会报类似下面的错误:
ERROR: 1 Can't create/write to file '/tmp/#sql_7a0e_0.MYI' (Errcode: 13)
这说明没有写权限,所以需要修改/tmp目录的权限:

chmod 1777 /tmp

同样,mysql_install_db初始时如果提示已废弃,则使用如下方法:

bin/mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

初始化完成后,提供配置文件和服务启动脚本。

shell> cp -a support-files/mysql.server /etc/init.d/mysqld
shell> cp -a support-files/my-default.cnf /etc/my.cnf  

# 修改my.cnf的datadir
shell> vim /etc/my.cnf 
[mysqld]
datadir=/mydata/data

如果是centos7,则提供如下服务启动脚本(如有必要,修改pid文件路径)。

shell> cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/usr/local/mysql-5.7.19/bin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

修改"root@localhost"密码。

shell> mysql
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> q

Master端:linuxprobe

1.2 通用二进制包安装mysql

通用二进制包相当于windows中的便携版软件,解压后稍微配置下就可以直接使用,不用安装。

mysql通用二进制版官方下载地址:

  • MySQL 5.6通用二进制包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.35-linux-glibc2.12-x86_64.tar.gz
  • MySQL 5.7通用二进制包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.12-x86_64.tar.gz

其中文件中的glibc2.12表示的是Linux系统的glibc版本要比2.12新,可以使用ldd --version查看glibc版本。在CentOS 6上glibc默认就是2.12的,所以无需顾虑。

shell> tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
shell> ln -s /usr/local/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql

1.2.2 安装后的规范化操作

编译安装或通用二进制安装后,一般都需要做一些额外的操作,包括设置环境变量、输出头文件和库文件、设置man路径。

echo "export PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
chmod +x /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
echo "MANPATH /usr/local/mysql/man" >>/etc/man.config

echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig
ln -s /usr/local/mysql/include /usr/include/mysql

本文实例为大家分享CentOS 7.2 Yum安装mysql5.6的方法,供大家参考,具体内容如下

1.3.2 初始化

shell> ln -s /usr/local/mysql-5.6.37 /usr/local/mysql
shell> cd /usr/local/mysql
shell> useradd -r -s /sbin/nologin mysql
shell> mkdir -p /mydata/data
shell> chown -R mysql.mysql /mydata/data
shell> chmod o-rx /mydata/data
shell> chown -R mysql.mysql /usr/local/mysql
shell> scripts/mysql_install_db --user=mysql --datadir=/mydata/data
shell> cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
shell> cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
shell> chown -R root.root /usr/local/mysql
# 修改my.cnf的datadir、socket、log和pid路径。
shell> vim /etc/my.cnf 
[mysqld]
datadir=/mydata/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/mydata/data/mysql.log
pid-file=/mydata/data/mysqld.pid

注:mysql 5.7建议采用如下语句进行初始化。

/usr/local/mysql/bin/mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

如果是centos7,则提供systemd风格的服务管理脚本,并确认是否要修改pid文件路径。

3. mariadb安装

mariadb基本上算是mysql的另一个实现,绝大多数以及基础功能上和MySQL都相同。具体到安装上,也基本完全一样。

在centos7上,直接yum install mysql-server将默认安装mariadb,如果配置了mysql的yum源,需要指定"mysql-community-server"才表示安装mysql。

以下仅提供mariadb的各项资源下载地址,具体安装方法见前文对应mysql安装方法。个人建议,将mariadb的服务启动脚本阅读一遍,和MySQL的做个比较。

mariadb的镜像站点:

[mariadb]
name=mariadb
baseurl=http://yum.mariadb.org/10.2.6/centos/6.6/$basearch/
#baseurl=http://yum.mariadb.org/10.2.6/centos/7.2/$basearch/
enabled=1
gpgcheck=0

mariadb各通用二进制版:

mariadb各种二进制版本:澳门金莎娱乐手机版,https://downloads.mariadb.org/mariadb/+releases/

mariadb 10.2.6 systemd版(centos7):http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/bintar-linux-systemd-x86_64/mariadb-10.2.6-linux-systemd-x86_64.tar.gz

mariadb 10.2.6 非systemd版(centos6):http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/bintar-linux-x86_64/mariadb-10.2.6-linux-x86_64.tar.gz

mariadb源码包: http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.6/source/mariadb-10.2.6.tar.gz

 

# 安装mysql
[root@linuxprobe~]# yum --enablerepo=centos-sclo-rh -y install rh-mysql56-mysql-server
# 加载环境变量
[root@linuxprobe ~]# scl enable rh-mysql56 bash
[root@linuxprobe ~]# mysql -V 
mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapper
[root@linuxprobe ~]# which mysql 
/opt/rh/rh-mysql56/root/usr/bin/mysql
# 设置mysql开机自启动
root@linuxprobe ~]# vi /etc/profile.d/rh-mysql56.sh
#!/bin/bash
source /opt/rh/rh-mysql56/enable
export X_SCLS="`scl enable rh-mysql56 'echo $X_SCLS'`"




[4] Enable MySQL 5.6 and Configure initial settings.
[root@linuxprobe ~]# vi /etc/opt/rh/rh-mysql56/my.cnf.d/mysql-server.cnf
# add follows within [mysqld] section
[mysqld]
character-set-server=utf8
[root@linuxprobe ~]# systemctl start rh-mysql56-mysqld
[root@linuxprobe ~]# systemctl enable rh-mysql56-mysqld
[root@linuxprobe ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
 SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
# set root password
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
# remove anonymous users
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
# disallow root login remotely
Disallow root login remotely? [Y/n] y
 ... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
# remove test database
Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
# reload privilege tables
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!




[root@linuxprobe ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> select user,host,password from mysql.user; 
+------+-----------+-------------------------------------------+
| user | host | password     |
+------+-----------+-------------------------------------------+
| root | localhost | *E2ACEC2F2DA384EE6753673365DFEF35F0C272C9 |
| root | 127.0.0.1 | *E2ACEC2F2DA384EE6753673365DFEF35F0C272C9 |
| root | ::1 | *E2ACEC2F2DA384EE6753673365DFEF35F0C272C9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> show databases; 
+--------------------+
| Database  |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye

1.3 编译源码安装mysql

建议别没事找事,尝试编译装MySQL,完全是吃力不讨好的事。如果确实要编译安装,把my.cnf、服务管理脚本、编译选项等涉及到运行文件路径的项确保相同,例如pid文件、socket文件、datadir路径、log文件。并保证mysql涉及到的目录所有者和所属组都是mysql,例如默认的pid路径/var/run/mysql/。

mysql源码包下载地址:

  • MySQL 5.6源码包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.37.tar.gz

  • MySQL 5.7源码包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19.tar.gz

1.3.2 初始化

shell> ln -s /usr/local/mysql-5.6.37 /usr/local/mysql
shell> cd /usr/local/mysql
shell> useradd -r -s /sbin/nologin mysql
shell> mkdir -p /mydata/data
shell> chown -R mysql.mysql /mydata/data
shell> chmod o-rx /mydata/data
shell> chown -R mysql.mysql /usr/local/mysql
shell> scripts/mysql_install_db --user=mysql --datadir=/mydata/data
shell> cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
shell> cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
shell> chown -R root.root /usr/local/mysql
# 修改my.cnf的datadir、socket、log和pid路径。
shell> vim /etc/my.cnf 
[mysqld]
datadir=/mydata/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/mydata/data/mysql.log
pid-file=/mydata/data/mysqld.pid

注:mysql 5.7建议采用如下语句进行初始化。

/usr/local/mysql/bin/mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

如果是centos7,则提供systemd风格的服务管理脚本,并确认是否要修改pid文件路径。

配置CentOS SCLo源

Installation Layout Options

  • -DCMAKE_INSTALL_PREFIX=dir_name   # MySQL的安装位置
  • -DINSTALL_PLUGINDIR=dir_name      # 插件安装的目录
  • -DMYSQL_DATADIR=dir_name        # MySQL的data dir
  • -DSYSCONFDIR=dir_name        # MySQL默认的配置文件(my.cnf)路径
  • -DTMPDIR=dir_name          # 临时文件存放路径,在MySQL5.6.16中才开始提供该选项

Feature Options

  • -DDEFAULT_CHARSET=charset_name

设置默认字符集,默认为latin1。例如,binary,ascii,big5 ,gb2312,gbk,latin1,latin2,latin5,latin7,utf8。

在cmake/character_sets.cmake文件中的CHARSETS_AVAILABLE定义了允许使用的字符集列表。

字符集的设置可以在MySQL服务启动的时候使用"--character_set_server"选项来指定。

  • -DDEFAULT_COLLATION=collation_name

设置默认的排序规则。默认排序规则为latin1_swedish_ci。使用SHOW COLLATION语句可以查看每种字符集可以使用的排序规则。

排序规则的设置可以在MySQL服务启动的时候使用--collation_server选项来指定。

  • -DENABLED_LOCAL_INFILE=bool

Whether to enable LOCAL capability in the client library for LOAD DATA INFILE.
This option controls client-side LOCAL capability, but the capability can be set on the server side at server startup with the --local-infile option.

  • -DMYSQL_TCP_PORT=port_num

指定MySQL的TCP端口,默认是3306。可以在启动服务时使用--port选项指定。

  • -DMYSQL_UNIX_ADDR=file_name

指定MySQL的套接字路径,必须是绝对路径,默认是/tmp/mysql.sock。可以在启动服务时使用--port选项指定。

  • -DWITH_EXTRA_CHARSETS=name
    Which extra character sets to include:

    • all: All character sets. This is the default.
    • complex: Complex character sets.
    • none: No extra character sets.
  • -DWITH_INNODB_MEMCACHED=bool

Whether to generate memcached shared libraries (libmemcached.so and innodb_engine.so).

  • -DWITH_SSL={ssl_type|path_name}
    The type of SSL support to include (if any) or the path name to the OpenSSL installation to use.

    • ssl_type can be one of the following values:
      • no: No SSL support. This is the default before MySQL 5.6.6. As of 5.6.6, this is no longer a permitted value and the default is bundled.
      • yes: Use the system SSL library if present, else the library bundled with the distribution.
      • bundled: Use the SSL library bundled with the distribution. This is the default as of MySQL 5.6.6.
      • system: Use the system SSL library.
    • path_name, permitted for MySQL 5.6.7 and after, is the path name to the OpenSSL installation to use. Using this can be preferable to using the ssl_type value of system, for it can prevent CMake from detecting and using an older or incorrect OpenSSL version installed on the system. (Another permitted way to do the same thing is to set the CMAKE_PREFIX_PATH option to path_name.).
  • -DWITH_ZLIB=zlib_type

    • bundled: Use thezliblibrary bundled with the distribution. This is the default.
    • system: Use the system zlib library.

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

2.1 mysql多实例配置过程

本文介绍第二种方法,并采用rpm包安装的mysql实现多实例。再次说明,无论使用何种方式安装mysql,都可以实现多实例,其实看明白下面配置的过程就知道了。

创建并设置datadir,并初始化、分别提供配置文件。

shell> mkdir -p /mydata/{3306,3307}/data
shell> chown -R mysql.mysql /mydata/{3306,3307}/data
shell> mysql_install_db --datadir=/mydata/3306/data --user=mysql
shell> mysql_install_db --datadir=/mydata/3307/data --user=mysql
shell> cp /etc/my.cnf /mydata/3306/my.cnf
shell> cp /etc/my.cnf /mydata/3307/my.cnf

如果是设置mysql 5.7的多实例,则初始化时使用如下命令替换上面的mysql_install_db

shell> mysqld --initialize-insecure --datadir=/mydata/3306/data --user=mysql
shell> mysqld --initialize-insecure --datadir=/mydata/3307/data --user=mysql

分别修改两个配置文件。

#以下是3306实例的配置文件要修改的部分。
shell> vim /mydata/3306/my.cnf
[mysqld]
port=3306
datadir=/mydata/3306/data
socket=/mydata/3306/data/mysql.sock
server_id=1
[mysqld_safe]
log-error=/mydata/3306/data/mysqld.log
pid-file=/mydata/3306/data/mysqld.pid

#以下是3307实例的配置文件要修改部分。
shell> vim /mydata/3307/my.cnf
[mysqld]
port=3307
datadir=/mydata/3307/data
socket=/mydata/3307/data/mysql.sock
server_id=2
[mysqld_safe]
log-error=/mydata/3307/data/mysqld.log
pid-file=/mydata/3307/data/mysqld.pid

2.3 提供systemd服务管理脚本

  • 以下是3306实例的管理脚本/usr/lib/systemd/system/mysqld3306.service。
  • 由于我的示例中mysql是采用rpm安装,所以mysql的basedir为/usr,如果是编译安装或通用二进制安装,则对应修改下面脚本中的basedir变量。
  • 由于rpm包安装在centos 7上,已经没有mysqld_safe命令,因此使用mysqld来启动mysql实例。
  • 将此管理脚本复制为/usr/lib/systemd/system/mysqld3307.service,再将其内的3306修改为3307即可作为3307实例的服务管理脚本。
  • 此脚本不会初始化mysql(我把这部分代码删了)创建实例,所以启动服务前务必先初始化好对应的mysql实例。
  • 下面的脚本即可作为多实例服务管理脚本,也可以作为单实例服务管理脚本,只需将脚本名称改一改即可。
  • mysql为systemd自带了多实例服务管理脚本/usr/lib/systemd/system/mysqld@.service。我没有采用,但其设置方法可以借鉴下。
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/mydata/3306/data/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Start main service
ExecStart=/usr/sbin/mysqld --defaults-file=/mydata/3306/my.cnf --basedir=/usr --daemonize $MYSQLD_OPTS
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

然后重载下systemd服务管理脚本。

systemctl daemon-reload

之后就可以使用下面的命令管理两个实例:

systemctl {start|stop|status|restart} mysqld3306
systemctl {start|stop|status|restart} mysqld3307

Slave 端:

1.2.1 初始化数据库

不使用rpm包安装,就需要对mysql进行初始化以创建一些文件、库和指定一些参数。但在初始化mysql前,要预先做一些操作。

shell> mkdir -p /mydata/data
shell> useradd -r -s /sbin/nologin mysql
shell> chown -R mysql.mysql /usr/local/mysql
shell> chown -R mysql.mysql /mydata/data
shell> cd /usr/local/mysql
shell> scripts/mysql_install_db --datadir=/mydata/data --user=mysql
shell> chown -R root.root /usr/local/mysql

执行mysql_install_db时会在/tmp下创建临时表,所以mysql用户需要对/tmp有写权限,否则执行实例初始化脚本时可能会报类似下面的错误:
ERROR: 1 Can't create/write to file '/tmp/#sql_7a0e_0.MYI' (Errcode: 13)
这说明没有写权限,所以需要修改/tmp目录的权限:

chmod 1777 /tmp

同样,mysql_install_db初始时如果提示已废弃,则使用如下方法:

bin/mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

初始化完成后,提供配置文件和服务启动脚本。

shell> cp -a support-files/mysql.server /etc/init.d/mysqld
shell> cp -a support-files/my-default.cnf /etc/my.cnf  

# 修改my.cnf的datadir
shell> vim /etc/my.cnf 
[mysqld]
datadir=/mydata/data

如果是centos7,则提供如下服务启动脚本(如有必要,修改pid文件路径)。

shell> cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/usr/local/mysql-5.7.19/bin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

修改"root@localhost"密码。

shell> mysql
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> q

1.1 rpm包安装mysql

直接yum安装mysql-server即可。但注意两点:

(1).centos7上,yum默认将安装mariadb。
(2).centos6上,yum默认安装的版本比较老(5.1版),要安装mysql 5.6或mysql 5.7,可以从官方下载,也可以使用以下配置的yum源。

cat <<eof>/etc/yum.repos.d/mysql.repo
[mysql]
name=MySQL
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
# baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
# baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
# baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
eof

此外,在sohu镜像站点也提供了mysql的各个rpm版本。地址:http://mirrors.sohu.com/mysql/

还需注意,配置了yum源后安装Mysql将使用mysql-community-*安装各mysql相关包,例如mysql-community-server。

安装完成后,启动mysqld。

shell> service mysqld start      # 或 systemctl start mysqld

如果启动失败,则可能需要初始化MySQL。

shell> mkdir -p /mydata/data
shell> chown -R mysql.mysql /mydata/data
shell> mysql_install_db --datadir=/mydata/data --user=mysql

如果使用mysql_install_db初始化时提示该命令已经废弃(5.7版本可能会如此提示),那么使用下面的命令进行初始化。

# 初始化时,为root@localhost创建一个临时密码存放在mysql.log中
shell> mysqld --initialize --datadir=/mydata/data --user=mysql
# 初始化时,为root@localhost创建一个空密码
shell> mysqld --initialize-insecure --datadir=/mydata/data --user=mysql

初始化后再启动,启动成功后连接数据库并修改root@localhost用户的密码,然后退出。

shell> mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> q

如果前面使用mysqld --initialize初始化数据库,那么将会为"root@localhost"创建一个密码,这将使得无法直接使用mysql命令连接数据库。可以先从mysql.log中筛选出创建的临时密码,然后再手动修改为"123456"。

shell> grep 'temporary password' /var/log/mysqld.log
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> q

yum安装MySQL

Feature Options

  • -DDEFAULT_CHARSET=charset_name

设置默认字符集,默认为latin1。例如,binary,ascii,big5 ,gb2312,gbk,latin1,latin2,latin5,latin7,utf8。

在cmake/character_sets.cmake文件中的CHARSETS_AVAILABLE定义了允许使用的字符集列表。

字符集的设置可以在MySQL服务启动的时候使用"--character_set_server"选项来指定。

  • -DDEFAULT_COLLATION=collation_name

设置默认的排序规则。默认排序规则为latin1_swedish_ci。使用SHOW COLLATION语句可以查看每种字符集可以使用的排序规则。

排序规则的设置可以在MySQL服务启动的时候使用--collation_server选项来指定。

  • -DENABLED_LOCAL_INFILE=bool

Whether to enable LOCAL capability in the client library for LOAD DATA INFILE.
This option controls client-side LOCAL capability, but the capability can be set on the server side at server startup with the --local-infile option.

  • -DMYSQL_TCP_PORT=port_num

指定MySQL的TCP端口,默认是3306。可以在启动服务时使用--port选项指定。

  • -DMYSQL_UNIX_ADDR=file_name

指定MySQL的套接字路径,必须是绝对路径,默认是/tmp/mysql.sock。可以在启动服务时使用--port选项指定。

  • -DWITH_EXTRA_CHARSETS=name
    Which extra character sets to include:

    • all: All character sets. This is the default.
    • complex: Complex character sets.
    • none: No extra character sets.
  • -DWITH_INNODB_MEMCACHED=bool

Whether to generate memcached shared libraries (libmemcached.so and innodb_engine.so).

  • -DWITH_SSL={ssl_type|path_name}
    The type of SSL support to include (if any) or the path name to the OpenSSL installation to use.

    • ssl_type can be one of the following values:
      • no: No SSL support. This is the default before MySQL 5.6.6. As of 5.6.6, this is no longer a permitted value and the default is bundled.
      • yes: Use the system SSL library if present, else the library bundled with the distribution.
      • bundled: Use the SSL library bundled with the distribution. This is the default as of MySQL 5.6.6.
      • system: Use the system SSL library.
    • path_name, permitted for MySQL 5.6.7 and after, is the path name to the OpenSSL installation to use. Using this can be preferable to using the ssl_type value of system, for it can prevent CMake from detecting and using an older or incorrect OpenSSL version installed on the system. (Another permitted way to do the same thing is to set the CMAKE_PREFIX_PATH option to path_name.).
  • -DWITH_ZLIB=zlib_type

    • bundled: Use thezliblibrary bundled with the distribution. This is the default.
    • system: Use the system zlib library.

Storage Engine Options

存储引擎是插件式的,可被静态编译到MySQL服务中,也可以动态编译成模块,编译成模块时需要使用INSTALL PLUGIN语句或者--plugin-load选项来启用。但某些插件是固化的,无法指定是静态编译还是动态编译。

InnoDB,MyISAM,MERGE,MEMORY和CSV存储引擎总是默认静态编译到MySQL服务中的,在编译安装的时候无需显式指定它们。

编译存储引擎的时候,使用-DWITH_enginename_STORAGE_ENGINE=1表示静态编译到MySQL。可选的引擎有:ARCHIVE、BLACKHOLE、EXAMPLE、FEDERATED、PARTITION(分区支持引擎)、PERFSCHEMA(Performance Schema)。如:

-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

使用-WITHOUT_enginename_STORAGE_ENGINE=1表示显式的排除这些引擎,即强制不使用它们。如:

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_enginename_STORAGE_ENGINE-WITHOUT_enginename_STORAGE_ENGINE都没有指定某些引擎的话,这些引擎默认动态编译成模块,如果无法动态编译成模块则不编译。

开启防火墙

1.2 通用二进制包安装mysql

通用二进制包相当于windows中的便携版软件,解压后稍微配置下就可以直接使用,不用安装。

mysql通用二进制版官方下载地址:

  • MySQL 5.6通用二进制包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.35-linux-glibc2.12-x86_64.tar.gz
  • MySQL 5.7通用二进制包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.12-x86_64.tar.gz

其中文件中的glibc2.12表示的是Linux系统的glibc版本要比2.12新,可以使用ldd --version查看glibc版本。在CentOS 6上glibc默认就是2.12的,所以无需顾虑。

shell> tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
shell> ln -s /usr/local/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
[root@linuxprobe ~]# firewall-cmd –add-service=mysql –permanent 
success 
[root@linuxprobe ~]# firewall-cmd –reload 
success

1.3 编译源码安装mysql

建议别没事找事,尝试编译装MySQL,完全是吃力不讨好的事。如果确实要编译安装,把my.cnf、服务管理脚本、编译选项等涉及到运行文件路径的项确保相同,例如pid文件、socket文件、datadir路径、log文件。并保证mysql涉及到的目录所有者和所属组都是mysql,例如默认的pid路径/var/run/mysql/。

mysql源码包下载地址:

  • MySQL 5.6源码包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.37.tar.gz

  • MySQL 5.7源码包下载:
    https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19.tar.gz

[root@linuxprobe ~]# 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.6.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> flush tables with read lock; # 锁表
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; #记住file的数值和position的值
+------------------+----------+--------------+------------------+-------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 400 |  |   |   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
#
[root@linuxprobe ~]# mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql # 备份数据库
Enter password: 
[root@linuxprobe ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.6.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> unlock tables; # 解锁
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@linuxprobe ~]# scp mysql_dump.sql vdevops.org@/tmp/
cp: cannot create regular file ‘vdevops.org@/tmp/': No such file or directory
[root@linuxprobe ~]# scp mysql_dump.sql vdevops.org:/tmp/
The authenticity of host 'vdevops.org (10.1.1.56)' can't be established.
ECDSA key fingerprint is f8:d2:55:54:8f:e8:43:e0:ee:aa:d6:8d:53:8c:8e:85.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'vdevops.org' (ECDSA) to the list of known hosts.
mysql_dump.sql 

转载请注明出处:http://www.cnblogs.com/f-ck-need-u/p/7590376.html

[root@linuxprobe~]# vi /etc/opt/rh/rh-mysql56/my.cnf.d/mysql-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# define uniq server ID
server-id=101
[root@linuxprobe ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> grant replication slave on *.* to replica@'%' identified by 'password';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

本文目录:
1. mysql单实例安装
  1.1 rpm安装mysql
  1.2 通用二进制包安装mysql
    1.2.1 初始化数据库
    1.2.2 安装后的规范化操作
  1.3 编译安装
    1.3.1 编译安装过程
    1.3.2 初始化
    1.3.3 规范化
    1.3.4 cmake编译MySQL时的选项说明
2. mysql多实例配置
  2.1 mysql多实例配置过程
  2.2 提供sysV服务管理脚本
  2.3 提供systemd服务管理脚本
3. mariadb安装

2.1 mysql多实例配置过程

本文介绍第二种方法,并采用rpm包安装的mysql实现多实例。再次说明,无论使用何种方式安装mysql,都可以实现多实例,其实看明白下面配置的过程就知道了。

创建并设置datadir,并初始化、分别提供配置文件。

shell> mkdir -p /mydata/{3306,3307}/data
shell> chown -R mysql.mysql /mydata/{3306,3307}/data
shell> mysql_install_db --datadir=/mydata/3306/data --user=mysql
shell> mysql_install_db --datadir=/mydata/3307/data --user=mysql
shell> cp /etc/my.cnf /mydata/3306/my.cnf
shell> cp /etc/my.cnf /mydata/3307/my.cnf

如果是设置mysql 5.7的多实例,则初始化时使用如下命令替换上面的mysql_install_db

shell> mysqld --initialize-insecure --datadir=/mydata/3306/data --user=mysql
shell> mysqld --initialize-insecure --datadir=/mydata/3307/data --user=mysql

分别修改两个配置文件。

#以下是3306实例的配置文件要修改的部分。
shell> vim /mydata/3306/my.cnf
[mysqld]
port=3306
datadir=/mydata/3306/data
socket=/mydata/3306/data/mysql.sock
server_id=1
[mysqld_safe]
log-error=/mydata/3306/data/mysqld.log
pid-file=/mydata/3306/data/mysqld.pid

#以下是3307实例的配置文件要修改部分。
shell> vim /mydata/3307/my.cnf
[mysqld]
port=3307
datadir=/mydata/3307/data
socket=/mydata/3307/data/mysql.sock
server_id=2
[mysqld_safe]
log-error=/mydata/3307/data/mysqld.log
pid-file=/mydata/3307/data/mysqld.pid

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:Yum编译安装MySQL,MySQL数据库基于Centos7【澳门金莎

关键词:

解析MYSQL显示表信息的方法,MySQL中的常用工具实

一.概述 mysqlshow客户端查找工具,能很快地查找存在哪些数据库,数据库中的表,表中的列或索引,和mysql客户端工具...

详细>>

8小时空闲后连接失效的解决,mysql超时机制

由于mysql默认8小时连接无访问,就会断开.为此查了一下资料,有同种比较简单的解决方案: mysql 8小时空闲后连接失效的...

详细>>

索引碎片的查询,索引阐述系列六

一 . dm_db_index_physical_stats 重要字段说明 1.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘...

详细>>

Apache与Tomcat区别联系,WEB开发基本知识

    小编利用asp.net的webform框架举办web应用程序的付出已经大致八年了,在全部开拓生涯中,也选用过一年asp.net的...

详细>>