原创作品，允许转载，转载时请务必以超链接形式标明文章
原始出处 、作者信息和本声明。否则将追究法律责任。
http://dgd2010.blog.51cto.com/1539422/1689171
|
1
|
/usr/local/mysql/bin/mysqldump
-uusername -hipaddress -ppassword -Pport --routines --events --triggers --single-transaction --flush-logs --master-data=1 –databases databasename
|
|
1
|
/usr/local/mysql/bin/mysqldump
-uusername -hipaddress -ppassword -Pport --routines --events --triggers --no-create-info --no-data --no-create-db
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
# Sat Aug 22 11:23:03 CST 2015
# Get mysql 5.5.38 for production use
# https://downloads.mariadb.com/archive/index/p/mysql/v/5.5.38
# https://downloads.mariadb.com/archives/mysql-5.5/mysql-5.5.38.tar.gz
wget -c https:
//downloads
.mariadb.com
/archives/mysql-5
.5
/mysql-5
.5.38-linux2.6-x86_64.
tar
.gz
# Remove some conflicts
rpm -e mysql-server mysql
userdel mysql
rm
-rf
/usr/local/mysql/
rm
-f
/etc/my
.cnf
# Set user and group
groupadd -r -g 27 mysql
useradd
-r -u 27 -g mysql mysql -c
"MySQL Server"
-d
/dev/null
-s
/sbin/nologin
groupadd -r mysql
useradd
-r -g mysql mysql -c
"MySQL Server"
-d
/dev/null
-s
/sbin/nologin
# Install MySQL database
tar
zxf mysql-5.5.38-linux2.6-x86_64.
tar
.gz
cp
-r mysql-5.5.38-linux2.6-x86_64
/usr/local/mysql
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --explicit_defaults_for_timestamp --skip-name-resolve
/usr/local/mysql/scripts/mysql_install_db
--basedir=
/usr/local/mysql
--datadir=
/data/mysql/data
--user=mysql --skip-name-resolve
# Run it temporarily
# /usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysqld_safe
--defaults-
file
=
/etc/my
.cnf &
# Set mysql commands
ln
-s
/usr/local/mysql/bin/mysql
/usr/bin/mysql
ln
-s
/usr/local/mysql/bin/mysqladmin
/usr/bin/mysqladmin
ln
-s
/usr/local/mysql/bin/mysqldump
/usr/bin/mysqldump
ln
-s
/usr/local/mysql/bin/mysqlbinlog
/usr/bin/mysqlbinlog
ln
-s
/usr/local/mysql/bin/mysql_config
/usr/bin/mysql_config
# set mysql root password, etc
/usr/local/mysql/bin/mysql_secure_installation
# Set mysql library
ln
-s
/usr/local/mysql/lib/libmysqlclient
.so.18.0.0
/usr/lib64/libmysqlclient
.so.18.0.0
ln
-s
/usr/local/mysql/lib/libmysqlclient
.so.18.0.0
/usr/lib64/libmysqlclient
.so.18
ln
-s
/usr/local/mysql/lib/libmysqlclient
.so.18.0.0
/usr/lib64/libmysqlclient
.so
ls
-l
/usr/lib64/libmysqlclient
.so.18.0.0
ls
-l
/usr/lib64/libmysqlclient
.so.18
ls
-l
/usr/lib64/libmysqlclient
.so
vim
/etc/ld
.so.conf.d
/mysql-x86_64
.conf
/usr/lib64/mysql
/usr/local/mysql/lib
ldconfig
# Set mysql replication
# master db
cp
/usr/local/mysql/support-files/my-small
.cnf
/etc/my
.cnf
sed
-i
"s@server-id\t= 1@server-id = 101@g"
/etc/my
.cnf
sed
-i
"s@#log-bin=mysql-bin@log-bin=mysql-bin@g"
/etc/my
.cnf
sed
-i
"s@#binlog_format=mixed@binlog_format=mixed@g"
/etc/my
.cnf
# slave db
cp
/usr/local/mysql/support-files/my-small
.cnf
/etc/my
.cnf
sed
-i
"s@server-id\t= 1@server-id = 103@g"
/etc/my
.cnf
# read errors from logs
cat
/usr/local/mysql/data/
`
hostname
`.err
# Some reference
# server-id = 101
# log-bin=/usr/local/mysql/data/bin-log
# max_binlog_size = 1500M
# binlog_cache_size = 128K
# binlog-do-db = devdbops
# binlog-ignore-db = mysql
# log-slave-updates
# expire_logs_day=2
# binlog_format=mixed
# end mysql temporarily
kill
-TERM `
ps
-ef |
awk
'/mysqld_safe/ && ! /awk/ {print $2}'
` ||
kill
-KILL `
ps
-ef |
awk
'/mysqld_safe/ && ! /awk/ {print $2}'
`
ps
-ef |
awk
'/mysqld_safe/ && ! /awk/ {print $2}'
kill
-TERM `
ps
-ef |
awk
'/mysqld/ && ! /awk/ {print $2}'
` ||
kill
-KILL `
ps
-ef |
awk
'/mysqld/ && ! /awk/ {print $2}'
`
ps
-ef |
awk
'/mysqld/ && ! /awk/ {print $2}'
# Setting sysvinit
cp
/usr/local/mysql/support-files/mysql
.server
/etc/init
.d
/mysql
chmod
+x
/etc/init
.d
/mysql
# Start mysql database
service mysql start
service mysql status
# some operation about replicaion
# master db
GRANT ALL PRIVILEGES ON *.* TO root@
"%"
IDENTIFIED BY
"root"
;
FLUSH PRIVILEGES;
CREATE USER
'dev'
@
'%'
IDENTIFIED BY
'dev'
;
CREATE DATABASE IF NOT EXISTS devdbops;
GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,UPDATE,SHOW VIEW ON devdbops.* TO
'dev'
@
"%"
;
USE devdbops;
CREATE TABLE `testtable` (
`
id
` int NOT NULL ,
`name` varchar(255) NULL ,
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`
id
`)
)
;
INSERT INTO `testtable` (`
id
`, `name`, `value`) VALUES (
'0'
,
'a'
,
'b'
);
# CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
# GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
# CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
# GRANT REPLICATION SLAVE ON devdbops.* to 'replication'@'%';
SHOW MASTER STATUS;
SHOW PROCESSLIST \G;
SHOW SLAVE HOSTS;
QUIT;
# slave db
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=
'192.168.1.101'
,MASTER_USER=
'root'
,MASTER_PASSWORD=
'root'
,MASTER_LOG_FILE=
'mysql-bin.000002'
,MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS \G;
# Read for test
|
