7aB9K1m0

1#!/bin/bash
2BASE_PATH=$(dirname $0)
3
4slave-exec() {
5    mysql --host slave-db-1 -uroot -p$MYSQL_SLAVE_PASSWORD -AN -e "$1"
6    mysql --host slave-db-2 -uroot -p$MYSQL_SLAVE_PASSWORD -AN -e "$1"
7}
8
9master-exec() {
10    mysql --host master-db -uroot -p$MYSQL_MASTER_PASSWORD -AN -e "$1"
11}
12
13sql() {
14    if [ $# -eq 1 ] ; then
15        master-exec "$1";
16    else
17        master-exec "USE $1; $2";
18    fi
19}
20
21echo "Waiting for mysql to get up"
22# Give 60 seconds for master and slave to come up
23sleep 60
24
25echo "Create MySQL Servers (master / slave repl)"
26echo "-----------------"
27
28
29echo "* Create replication user"
30
31slave-exec 'STOP SLAVE;';
32slave-exec 'RESET SLAVE ALL;';
33
34master-exec "CREATE USER '$MYSQL_REPLICATION_USER'@'%';"
35master-exec "GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD';"
36master-exec 'flush privileges;'
37
38
39echo "* Set MySQL01 as master on MySQL02 and MySQL03"
40MYSQL01_Position=$(eval "mysql --host master-db -uroot -p$MYSQL_MASTER_PASSWORD -e 'show master status \G' | grep Position | sed -n -e 's/^.*: //p'")
41MYSQL01_File=$(eval "mysql --host master-db -uroot -p$MYSQL_MASTER_PASSWORD -e 'show master status \G'     | grep File     | sed -n -e 's/^.*: //p'")
42MASTER_IP=$(eval "getent hosts master-db|awk '{print \$1}'")
43echo $MASTER_IP
44slave-exec "CHANGE MASTER TO master_host='master-db', master_port=3306, \
45        master_user='$MYSQL_REPLICATION_USER', master_password='$MYSQL_REPLICATION_PASSWORD', master_log_file='$MYSQL01_File', \
46        master_log_pos=$MYSQL01_Position;"
47
48echo "* Set MySQL02 as master on MySQL01"
49MYSQL02_Position=$(eval "mysql --host slave-db-1 -uroot -p$MYSQL_SLAVE_PASSWORD -e 'show master status \G' | grep Position | sed -n -e 's/^.*: //p'")
50MYSQL02_File=$(eval "mysql --host slave-db-1 -uroot -p$MYSQL_SLAVE_PASSWORD -e 'show master status \G'     | grep File     | sed -n -e 's/^.*: //p'")
51SLAVE_IP_1=$(eval "getent hosts slave-db-1|awk '{print \$1}'")
52echo $SLAVE_IP_1
53master-exec "CHANGE MASTER TO master_host='slave-db-1', master_port=3306, \
54        master_user='$MYSQL_REPLICATION_USER', master_password='$MYSQL_REPLICATION_PASSWORD', master_log_file='$MYSQL02_File', \
55        master_log_pos=$MYSQL02_Position;"
56        
57echo "* Set MySQL03 as master on MySQL01"
58MYSQL03_Position=$(eval "mysql --host slave-db-2 -uroot -p$MYSQL_SLAVE_PASSWORD -e 'show master status \G' | grep Position | sed -n -e 's/^.*: //p'")
59MYSQL03_File=$(eval "mysql --host slave-db-2 -uroot -p$MYSQL_SLAVE_PASSWORD -e 'show master status \G'     | grep File     | sed -n -e 's/^.*: //p'")
60SLAVE_IP_2=$(eval "getent hosts slave-db-2|awk '{print \$1}'")
61echo $SLAVE_IP_2
62master-exec "CHANGE MASTER TO master_host='slave-db-2', master_port=3306, \
63        master_user='$MYSQL_REPLICATION_USER', master_password='$MYSQL_REPLICATION_PASSWORD', master_log_file='$MYSQL03_File', \
64        master_log_pos=$MYSQL03_Position;"        
65
66echo "* Start Slave on both Servers"
67slave-exec "start slave;"
68
69echo "Increase the max_connections to 2000"
70master-exec 'set GLOBAL max_connections=2000';
71slave-exec 'set GLOBAL max_connections=2000';
72
73mysql --host slave-db-1 -uroot -p$MYSQL_MASTER_PASSWORD -e "show slave status \G"
74mysql --host slave-db-2 -uroot -p$MYSQL_MASTER_PASSWORD -e "show slave status \G"
75
76echo "MySQL servers created!"
77echo "--------------------"
78
79echo "Creating SQL structure"
80echo "--------------------"
81sql "CREATE DATABASE IF NOT EXISTS library";
82
83sql library "CREATE TABLE IF NOT EXISTS category (
84    id BIGINT AUTO_INCREMENT PRIMARY KEY, 
85    name VARCHAR(50) NOT NULL
86)";
87
88sql library "CREATE TABLE IF NOT EXISTS user (
89    id BIGINT AUTO_INCREMENT PRIMARY KEY, 
90    album BIGINT NOT NULL
91)";
92
93sql library "CREATE TABLE IF NOT EXISTS book (
94    id BIGINT AUTO_INCREMENT PRIMARY KEY, 
95    title VARCHAR(100) NOT NULL,
96    author VARCHAR(100) NOT NULL,
97    isbn VARCHAR(100) NOT NULL,
98    publishing_house VARCHAR(100) NOT NULL,
99    category BIGINT NOT NULL,
100    
101    CONSTRAINT book_category FOREIGN KEY (category) REFERENCES category(id)
102)";
103
104sql library "CREATE TABLE IF NOT EXISTS rent (
105    id BIGINT AUTO_INCREMENT PRIMARY KEY, 
106    book BIGINT NOT NULL,
107    user BIGINT NOT NULL,
108    rent_date DATETIME NOT NULL,
109    return_date DATETIME NOT NULL,
110    
111    CONSTRAINT rent_book FOREIGN KEY (book) REFERENCES book(id),
112    CONSTRAINT rent_user FOREIGN KEY (user) REFERENCES user(id)
113)";