docker run \
--name mysql-jira \
--restart always \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=adg_123456 \
-e MYSQL_DATABASE=jira_db \
-e MYSQL_USER=jira_user \
-e MYSQL_PASSWORD=jira_123456 \
-d \
mysql:5.7
SET NAMES 'utf8mb4';
alter database jira_db character set utf8mb4;
mkdir -p /data/docker/mysql/datadir /data/docker/mysql/conf /data/docker/mysql/log
vim /data/docker/mysql/conf/mysql-1.cnf
,内容如下:# 该编码设置是我自己配置的
[mysql]
default-character-set = utf8mb4
# 下面内容是 docker mysql 默认的 start
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 上面内容是 docker mysql 默认的 end
# 下面开始的内容就是我自己配置的
log-error=/var/log/mysql/error.log
default-storage-engine = InnoDB
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
lower_case_table_names = 1
max_allowed_packet = 50M
chmod -R 777 /data/docker/mysql/datadir /data/docker/mysql/log
chown -R 0:0 /data/docker/mysql/conf
whoami && id
,看到默认用户的 uid 是 0,所以这里才 chown 0docker run -p 3306:3306 --name cloud-mysql -v /data/docker/mysql/datadir:/var/lib/mysql -v /data/docker/mysql/log:/var/log/mysql -v /data/docker/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker exec -it cloud-mysql /bin/bash
mysql -u root -p
CREATE DATABASE wormhole DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
The MySQL startup configuration is specified in the file /etc/mysql/my.cnf, and that file in turn includes any files found in the /etc/mysql/conf.d directory that end with .cnf.Settings in files in this directory will augment and/or override settings in /etc/mysql/my.cnf. If you want to use a customized MySQL configuration,you can create your alternative configuration file in a directory on the host machine and then mount that directory location as /etc/mysql/conf.d inside the mysql container.
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
docker exec cloud-mysql /usr/bin/mysqldump -u root --password=123456 DATABASE_Name > /opt/backup.sql
docker exec -i cloud-mysql /usr/bin/mysql -u root --password=123456 DATABASE_Name < /opt/backup.sql
rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
rpm -Uvh https://mirror.webtatic.com/yum/el6/latest.rpm
yum install mysql55w mysql55w-server
,用同时生产 mysql 的组和用户service mysqld start
mysqladmin -u root password '123456'
vim /etc/my.cnf
cd /var/log/mysqld.log
tail -300 /var/log/mysqld.log
chown mysql.mysql /var/run/mysqld/
/opt
目录下创建一个目录 setups
用来存放各种软件安装包;在 /usr
目录下创建一个 program
用来存放各种解压后的软件包,下面的讲解也都是基于此习惯EPEL、RepoForge
,如果你出现 yum install XXXXX
安装不成功的话,很有可能就是你没有相关源,请查看我对源设置的文章wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.35.tar.gz
(大小:31 M)wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.11.tar.gz
(大小:47 M)cd /opt/setups
tar zxvf mysql-5.6.35.tar.gz
mv /opt/setups/mysql-5.6.35 /usr/local/
yum install -y make gcc-c++ cmake bison-devel ncurses-devel autoconf
cd /usr/local/mysql-5.6.35/
mkdir -p /usr/local/mysql/data
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_EXTRA_CHARSETS:STRING=utf8mb4 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1
make
,这个过程比较漫长,一般都在 30 分钟左右,具体还得看机子配置,如果最后结果有 error,建议删除整个 mysql 目录后重新解压一个出来继续处理make install
cp /usr/local/mysql-5.6.35/support-files/mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql
chkconfig mysql on
cp /usr/local/mysql-5.6.35/support-files/my-default.cnf /etc/my.cnf
rm -rf /usr/local/mysql-5.6.35/
groupadd mysql
#添加组useradd -g mysql mysql -s /bin/false
#创建用户mysql并加入到mysql组,不允许mysql用户直接登录系统chown -R mysql:mysql /usr/local/mysql/data
#设置MySQL数据库目录权限/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --skip-name-resolve --user=mysql
iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
service iptables save
service iptables restart
vim /etc/selinux/config
SELINUX=enforcing
改为 SELINUX=disabled
ln -s /usr/local/mysql/bin/mysql /usr/bin
ln -s /usr/local/mysql/bin/mysqladmin /usr/bin
ln -s /usr/local/mysql/bin/mysqldump /usr/bin
ln -s /usr/local/mysql/bin/mysqlslap /usr/bin
禁用 selinux:setenforce 0
wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
yum localinstall mysql57-community-release-el7-11.noarch.rpm
yum install mysql-community-server
一共 194M
配置文件:/etc/my.cnf
systemctl start mysqld
systemctl status mysqld
查看初次使用的临时密码:grep 'temporary password' /var/log/mysqld.log
find / -name "my.cnf"
,我查到的结果:/etc/my.cnf
/usr/local/mysql/my.cnf
/usr/local/mysql/mysql-test/suite/ndb/my.cnf
/usr/local/mysql/mysql-test/suite/ndb_big/my.cnf
.............
/usr/local/mysql/mysql-test/suite/ndb_rpl/my.cnf
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
service mysql start
systemctl start mysql
ps aux | grep mysql
mysql -uroot
SET PASSWORD = PASSWORD('123456');FLUSH PRIVILEGES;
UPDATE user SET authentication_string=PASSWORD('123456') where USER='root';FLUSH PRIVILEGES;
mysql -uroot -p
service mysql stop
systemctl stop mysql
/usr/local/mysql/bin/mysqld --skip-grant-tables --user=mysql
mysql -u root mysql
或者:mysql -h 127.0.0.1 -u root -P 3306 -p
UPDATE user SET Password=PASSWORD('123456') where USER='root';FLUSH PRIVILEGES;
service mysql restart
systemctl restart mysql
vim /etc/my.cnf
中不能有:bind-address = 127.0.0.1
GRANT ALL PRIVILEGES ON *.* TO '数据库用户名'@'%' IDENTIFIED BY '数据库用户名的密码' WITH GRANT OPTION;
flush privileges;
vim /etc/my.cnf
中必须有:bind-address = 127.0.0.1
GRANT ALL PRIVILEGES ON *.* TO '数据库用户名'@'127.0.0.1' IDENTIFIED BY '数据库用户名的密码' WITH GRANT OPTION;
flush privileges;
set global validate_password_policy=0; #密码强度设为最低等级
set global validate_password_length=6; #密码允许最小长度为6
set password = password('新密码');
FLUSH PRIVILEGES;
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'youmeek.nm.id'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
select version(), @@sql_mode;
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
cd /var/log/ && sz messages
Jul 6 21:49:14 VM_123_201_centos kernel: Out of memory: Kill process 19452 (httpd) score 36 or sacrifice child
Jul 6 21:49:14 VM_123_201_centos kernel: Killed process 19452 (httpd) total-vm:516404kB, anon-rss:36088kB, file-rss:168kB, shmem-rss:12kB
[mysqld]
table_definition_cache=400
table_open_cache=256
innodb_buffer_pool_size = 64M
max_connections = 100
dd if=/dev/zero of=/swapfile bs=1M count=1024
mkswap /swapfile
swapon /swapfile
vim /etc/fstab
/swapfile swap swap defauluts 0 0
reboot
主库操作步骤
mkdir -p /usr/local/mysql/data/mysql-bin
vim /etc/my.cnf
,log-bin = /usr/local/mysql/data/mysql-bin
binlog-do-db=ssm
SHOW VARIABLES LIKE '%slow_query_log%';
,如果显示 OFF 则表示关闭,ON 表示开启SHOW MASTER STATUS;
grant replication slave on *.* to 'slave01'@'192.168.1.135' identified by '123456';
flush privileges;
从库操作步骤
SHOW VARIABLES LIKE '%slow_query_log%';
,如果显示 OFF 则表示关闭,ON 表示开启。mysql -h 192.168.1.105 -u slave01 -p
,必须要连上下面的操作才有意义。service iptables stop
iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
service iptables save
service iptables restart
vim /etc/my.cnf
,把 server-id 改为跟主库不一样 CHANGE MASTER TO
master_host='192.168.1.113',
master_user='slave01',
master_password='123456',
master_port=3306,
master_log_file='mysql3306-bin.000006',>>>这个值复制刚刚让你记录的值
master_log_pos=1120;>>>这个值复制刚刚让你记录的值
执行该 SQL 语句,启动 slave 同步:START SLAVE;
执行该 SQL 语句,查看从库机子同步状态:SHOW SLAVE STATUS;
在查看结果中必须下面两个值都是 Yes 才表示配置成功:
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
如果你的 Slave_IO_Running 是 No,一般如果你是在虚拟机上测试的话,从库的虚拟机是从主库的虚拟机上复制过来的,那一般都会这样的,因为两台的 MySQL 的 UUID 值一样。你可以检查从库下的错误日志:cat /usr/local/mysql/data/mysql-error.log
vim /usr/local/mysql/data/auto.cnf
,把配置文件中的:server-uuid 值随便改一下,保证和主库是不一样即可。