安装mysql
1、获取安装包:
wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz
解压:tar -zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz
移动到mysql-5.6.43:mv mysql-5.6.43-linux-glibc2.12-x86_64/* mysql/
2、先检查是否有mysql用户组和mysql用户
groups mysql
创建mysql用户组和mysql用户
groupadd mysql
useradd -r -g mysql mysql
3、授权mysql数据存放目录给mysql用户
进入mysql执行:chown -R mysql:mysql data
安装perl
yum -y install perl perl-devel autoconf
4、初始化数据库
./mysql_install_db --basedir=/usr/data/mysql/mysql --datadir=/usr/data/mysql/mysql/data --user=mysql
若再报少库libaio*
yum install libaio* -y
5、拷贝 my-default.cnf到/etc/my.cnf
cd …/support-files/
cp my-default.cnf /etc/my.cnf
向/etc/init.d中添加mysql的启动服务
init.d目录包含许多系统各种服务的启动和停止脚本。
cd …/support-files/
cp mysql.server /etc/init.d/mysqld
修改/etc/init.d/mysqld权限
cd /etc/init.d
chmod 755 ./mysqld
6、修改配置
vi /etc/init.d/mysqld
basedir=/usr/data/mysql/mysql
datadir=/usr/data/mysql/mysql/data
修改 vi /etc/my.cnf
[client]
port = 3306
default-character-set = utf8
[mysqld]
federated
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
port = 3306
basedir = /usr/data/mysql/mysql
datadir = /usr/data/mysql/data
socket = /tmp/mysql.sock
user = mysql
skip-external-locking
key_buffer_size = 16M
max_connections = 1000
max_allowed_packet = 16M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
character-set-server = utf8
explicit_defaults_for_timestamp = 1
slow-query-log-file = /usr/data/mysql/data/logs/slow-query.log
long_query_time = 3
log-bin=mysql-bin
server-id=1
binlog_format=mixed
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server = utf8
lower_case_table_names=1
log-bin-trust-function-creators=1
group_concat_max_len=102400
collation_server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default-storage-engine = InnoDB
innodb_data_home_dir = /usr/data/mysql/data/innodb
innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:500M;ibdata5:500M;ibdata6:500M:autoextend
innodb_log_group_home_dir = /usr/data//mysql/data
innodb_buffer_pool_size = 512M
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
7、添加环境变量
vi /etc/profile
export JAVA_HOME=/usr/data/java/jdk1.8.0_121
export JRE_HOME=${JAVA_HOME}/jre
export MYSQL_HOME=/usr/data/mysql/mysql
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin
export MYSQL_path=${MYSQL_HOME}/bin
export PATH=$PATH:${JAVA_PATH}:${MYSQL_path}
重新编译 source /etc/profile
8、启动mysql
service mysqld start
查看MySQL的状态和停止MySQL
service mysqld status
service mysqld stop
9、修改MySQL的登录密码
进入skip-grant-tables模式
先停止mysql
mysqld_safe --skip-grant-tables
修改root用户密码并刷新权限
mysql
use mysql;
update user set password=password(“Zdq6NSDcmxEzaVFg398=”) where user=“root”;
flush privileges;
重新启动登录 mysql -u root -p
授权远程登录
grant all privileges on . to ‘root’@’%’ identified by ‘Zdq6NSDcmxEzaVFg398=’ with grant option;
flush privileges;
重启mysql
mysql主从
119.23.235.192主
119.23.39.184从
1、修改主配置文件
vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog_format=mixed
2、重启主,登录MySQL,授权给从
grant replication slave on . to ‘slave’@‘119.23.39.184’ identified by ‘slave’;
注意:119.23.39.184(此ip为从服务器的ip)
flush privileges;
查看主状态
show master status;
记录binlog文件和位置
3、修改从配置文件
vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=2 #设置server-id
binlog_format=mixed
4、重启从,并登录
执行同步SQL语句(需要主服务器主机名,登陆凭据,二进制文件的名称和位置):
change master to master_host=‘119.23.235.192’,master_user=‘slave’,master_password=‘slave’,master_log_file=‘mysql-bin.000001’,master_log_pos=621;
开启同步
start slave;
查看slave状态:
show slave status \G;
最终主配置文件
[client]
default-character-set= utf8
[mysqld]
federated
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog_format=mixed
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server = utf8
lower_case_table_names=1
log-bin-trust-function-creators=1
group_concat_max_len=102400
collation_server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
从配置文件
[client]
default-character-set= utf8
[mysqld]
federated
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
log-bin=mysql-bin #开启二进制日志
server-id=2 #设置server-id
binlog_format=mixed
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server = utf8
lower_case_table_names=1
log-bin-trust-function-creators=1
group_concat_max_len=102400
collation_server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES