原创

MySQL生产环境_(安装)_搭建主从(rpm方式安装)



1:安装MySQL
mysql官网下载:http://dev.mysql.com/downloads/mysql/
但如果你的下载网速不好的话也可以点下面的链接下载自己想要的版本
http://mirrors.sohu.com/mysql/MySQL-5.7/
我用的是(CentOs7.4)下载的是: mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar文件
1. 检查系统自带的MySQL及相关RPM包,是否安装
rpm -qa | grep -i mysql
如果有安装,则移除(rpm –e 名称)
yum -y remove mysql
 2. 创建用户和组(如果已经创建则跳过)
groupadd mysql;useradd -r -g mysql mysql

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
tar -xvf  mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
yum install perl
yum install net-tools
[root@localhost mysql] # rpm -qa | grep -i mariadb

   mariadb-libs-5.5.52-1.el7.x86_64

[root@localhost mysql]# rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64

yum -y install autoconf
依赖安装
[root@localhost ~]# rpm -ivh mysql*.rpm
warning: mysql-community-client-5.7.19.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
 mariadb-libs is obsoleted by mysql-community-libs-5.7.18-1.el7.x86_64
 mariadb-libs is obsoleted by mysql-community-libs-compat-5.7.18-1.el7.x86_64
 perl(JSON) is needed by mysql-community-test-5.7.18-1.el7.x86_64

rpm -qa |grep mariadb
yum remove -y mariadb-libs
yum install perl-JSON.noarch
rpm -qa |grep mariadb
rpm -ivh mysql*.rpm
如果出现错误:warning: mysql-community-client-5.7.19.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
解决办法加参数:--force --nodeps  
[root@node1 ~]# rpm -ivh mysql*.rpm --force --nodeps
warning: mysql-community-client-5.7.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.18-1.e################################# [  8%]
   2:mysql-community-libs-5.7.18-1.el7################################# [ 17%]
   3:mysql-community-client-5.7.18-1.e################################# [ 25%]
   4:mysql-community-server-5.7.18-1.e################################# [ 33%]
   5:mysql-community-devel-5.7.18-1.el################################# [ 42%]
   6:mysql-community-embedded-5.7.18-1################################# [ 50%]
   7:mysql-community-embedded-devel-5.################################# [ 58%]
   8:mysql-community-test-5.7.18-1.el7################################# [ 67%]
   9:mysql-community-libs-compat-5.7.1################################# [ 75%]
  10:mysql-community-embedded-compat-5################################# [ 83%]
  11:mysql-community-server-minimal-5.################################# [ 92%]
  12:mysql-community-minimal-debuginfo################################# [100%]
rpm -ivh mysql*.rpm --nosignature
[root@slsf01 opt]# service mysqld start

2、把MySQL服务进程停掉
[root@slsf01 opt]# service mysqld stop
Starting mysqld (via systemctl):                           [  OK  ]


3、把/var/lib/mysql整个目录移到/home/data
mv /var/lib/mysql /opt/mysql/

4:主库配置文件
vi /etc/my.cnf
[mysqld]
#
# 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
#
# 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
datadir=/opt/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
user=mysql
character-set-server=utf8
default-storage-engine=innodb
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin_trust_function_creators=true
default_password_lifetime=0
log_bin  = /var/log/mysql/mysql-bin.log    
server-id=2014
log_bin_trust_function_creators=true
default_password_lifetime=0
open_files_limit=65535
lower_case_table_names=1
event_scheduler=1
max_allowed_packet=512M
interactive_timeout=28800
wait_timeout=28800
expire_logs_days=15
init_connect='SET autocommit=1'
max_user_connections=4000
max_connections=4000
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16
explicit_defaults_for_timestamp
#slow_query_sql
slow_launch_time=1
slow_query_log=1
slow_query_log_file=/opt/mysql/mysql-slow.log

# files
binlog_format=ROW
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000

# buffers
innodb_buffer_pool_size=80000M
innodb_buffer_pool_instances=1
#innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
join_buffer_size=3200K
sort_buffer_size=3200K

# innodb
#innodb_checksums=0
innodb_checksum_algorithm=NONE
innodb_doublewrite=0
innodb_support_xa=on
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
innodb_spin_wait_delay= 6

# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

启动数据库
[root@slsf01 opt]# service mysqld start

[root@slsf01 opt]# ps -ef|grep mysql
root      3746     1  0 17:01 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/opt/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/runmysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     3947  3746  0 17:01 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/opt/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

下面我们来看下root账户的随机初始密码,执行命令:vi /var/log/mysqld.log

[root@slsf02 /]# mysql -uroot -hlocalhost -p
SET PASSWORD = PASSWORD('Zrsf.20712');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Zrsf.27012' WITH  GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'Zrsf.27012' WITH  GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'Zrsf.27012' WITH GRANT OPTION;
update user set authentication_string=PASSWORD('Zrsf.27012') where user='root';


主库:
mysql> grant replication slave on *.* to 'repl'@'192.168.0.4' identified by 'Repl@2018';
mysql> flush privileges;
mysql> show master status\G
mysql> quit

丛库:
mysql>change master to master_host='192.168.1.1',master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154, master_user='reepl3252',master_password='123456'
mysql> show slave status\G    #查看slave的状态信息

mysql> show variables like "%character%";show variables like "%collation%";

显示为

 
注意字符集:
mysql>show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client    | utf8                      |
| character_set_connection | utf8                      |
| character_set_database  | utf8                      |
| character_set_filesystem | binary                    |
| character_set_results    | utf8                      |
| character_set_server    | utf8                      |
| character_set_system    | utf8                      |
| character_sets_dir      | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>show variables like "%collation%";
+----------------------+-----------------+
| Variable_name        | Value          |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database  | utf8_unicode_ci |
| collation_server    | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)


正文到此结束
Loading...