MySQL集群

#首先在node-4、node-5、node-6上安装MySQL
#配置MySQL 5.7的yum源

sudo tee -a /etc/yum.repos.d/mysql-community.repo << EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/\$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
EOF


#查看mysql源的信息
yum repolist enabled | grep mysql

#安装mysql的server
sudo yum install -y mysql-community-server

#启动mysql
sudo service mysqld start

#获取启动日志中的默认初始密码
#sudo grep 'temporary password' /var/log/mysqld.log

#获取密码并赋给一个变量
PASSWORD=`sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}'` 

#使用root用户登录
mysql -uroot -p$PASSWORD

#修改root用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'XiaoNiu_123!';

#修改mysql远程登录权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XiaoNiu_123!' WITH GRANT OPTION;
FLUSH PRIVILEGES; 

----------------------------------------------------------------------------------------------------
安装mycat
----------------------------------------------------------------------------------------------------

#然后在node-3安装JDK并配置环境变量

#然后在node-3安装mycat
#上传Mycat-server-1.6.5-release-20171008170112-linux.tar.gz安装包


#修改conf目录下主要以下三个注意配置文件
	server.xml是Mycat服务器参数调整和用户授权的配置文件
	schema.xml是逻辑库定义和表以及分片定义的配置文件
	rule.xml是分片规则的配置文件

#修改server.xml(修改了mycat的用户和逻辑的database)

<user name="xiaoniu" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">bigdata</property>
</user>

<user name="user">
        <property name="password">user</property>
        <property name="schemas">bigdata</property>
        <property name="readOnly">true</property>
</user>


#修改schema.xml（配置逻辑库下的逻辑表，已经数据存放的mysql节点）
    <schema name="bigdata" checkSQLschema="false" sqlMaxLimit="100">
        <!-- auto sharding by id (long) -->
        <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

        <!-- global table is auto cloned to all defined data nodes ,so can join
            with any table whose sharding node is in the same data node -->
        <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

        <!-- random sharding using mod sharind rule -->
        <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
               rule="mod-long" />
    </schema>

    <dataNode name="dn1" dataHost="cs4" database="db1" />
    <dataNode name="dn2" dataHost="cs5" database="db2" />
    <dataNode name="dn3" dataHost="cs6" database="db3" />

    <dataHost name="cs4" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.170.134:3306" user="root" password="XiaoNiu_123!">
        </writeHost>
    </dataHost>

    <dataHost name="cs5" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.170.135:3306" user="root" password="XiaoNiu_123!">
        </writeHost>
    </dataHost>

    <dataHost name="cs6" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.170.136:3306" user="root" password="XiaoNiu_123!">
        </writeHost>
    </dataHost>

#在三台mysql上分别创建数据库db1、db2、db3
#然后在每一个database中创建表，有三张（travelrecord、company、hotnews）注意主键的名称, 注意不要指定主键自增, 主键自增是 Mycat 维护的

CREATE TABLE `company` (
`id` BIGINT(20) NOT NULL,
`name` VARCHAR(50),
PRIMARY KEY(`id`)
);

CREATE TABLE `hotnews` (
`id` BIGINT(20) NOT NULL,
`name` VARCHAR(50),
PRIMARY KEY(`id`)
);

CREATE TABLE `travelrecord` (
`id` BIGINT(20) NOT NULL,
`name` VARCHAR(50),
PRIMARY KEY(`id`)
);

#在node-4上启动mycat


# auto-sharding-long 默认一个节点存500万
# 

mysql -h 192.168.10.104 -P 8066 -u root -p123456