mysql出现Table is read only错误解决方案

迁移mysql数据库data目录下相应实例物理文件时,账号有授权,操作update语句时提示“mysql中Table is read only错误”。
数据库服务器环境
数据库启动用户组:mysql
数据库启动用户:mysql
数据库data目录:/home/data/mysql
解决方案:
chown -R mysql.mysql  /home/data/mysql
chmod -R 755 /home/data/mysql
##找出mysqladmin命令路径##
find / -name mysqladmin
##默认端口3306##
/usr/local/mysql/bin/mysqladmin -u root -p flush-tables
##非默认端口3306,如3307##
/usr/local/mysql/bin/mysqladmin -u root -p -P3307 flush-tables

mysql更改数据库utf8到utf8mb4方法

1.MariaDB设置方法
vim /etc/my.cnf
#修改内容如下所示:
[client]
default-character-set = utf8mb4
[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect=’SET NAMES utf8mb4′
skip-character-set-client-handshake=true
[mysql]
default-character-set = utf8mb4
# 验证方法,登陆mysql
SHOW VARIABLES WHERE Variable_name LIKE ‘character\_set\_%’ OR Variable_name LIKE ‘collation%’;
2.MySql设置方法
vim /etc/my.cnf
#修改内容如下所示:
[client]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect=’SET NAMES utf8mb4′
[mysql]
default-character-set = utf8mb4
# 验证方法,登陆mysql
SHOW VARIABLES WHERE Variable_name LIKE ‘character\_set\_%’ OR Variable_name LIKE ‘collation%’;
3.数据库连接的配置
数据库连接参数中:
characterEncoding=utf8会被自动识别为utf8mb4,也可以不加这个参数,会自动检测。
而autoReconnect=true是必须加上的。
4.将数据库和已经建好的表也转换成utf8mb4
更改数据库编码:
DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
更改表编码:
ALTER TABLE user_data_1_testCONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
如有必要,还可以更改列的编码
5.修改表字段编码
ALTER TABLE user_data_1_test change name name char(100) character set utf8mb4 collate utf8mb4_unicode_ci;
ALTER TABLE user_data_1_test change name name char(100) character set utf8mb4 collate utf8mb4_unicode_ci  NOT NULL COMMENT ‘名称’;

XtraBackup不停机不锁表热备mysql数据库

环境
mysql版本 Server version: 5.6.42-log MySQL Community Server
数据库data目录 /data/mysql/
binlog日志目录 /data/bin_logs/
备份目录 /data/backup/

完全备份脚本 (每日一次,00:10:00执行)
************************************************************
#!/bin/bash
base_dir=”/data/backup/`date +%F`”
[ -d ${base_dir} ] || mkdir ${base_dir}
[ -d ${base_dir}/full_`date +%F` ] && exit 0
innobackupex –defaults-file=/etc/my.cnf –user=root –password=’123456′ –no-timestamp ${base_dir}/full_`date +%F`
[ $? -eq 0 ] || echo “$(date +”%F_ %T”) 完全备份失败,请重新备份” >> /data/backup/backup.log
************************************************************

增量备份脚本 (每小时一次,xx:30:00执行)
************************************************************
#!/bin/bash
base_dir=”/data/backup/`date +%F`”
full_dir=”/data/backup/`date +%F`/full_`date +%F`”
inc_dir=”${base_dir}/inc_`date +%H`”
[ -d ${inc_dir} ] && exit 0
innobackupex –defaults-file=/etc/my.cnf –user=root –password=’123456′ –no-timestamp –incremental-basedir=${full_dir} –incremental ${inc_dir} &> /dev/null
[ $? -eq 0 ] || echo “$(date +”%F_ %T”) ${inc_dir}增量备份失败,请重新备份” >> /data/backup/backup.log
************************************************************

一键还原脚本
************************************************************
#!/bin/bash
base_dir=”/data/backup/`date +%F`”
full_dir=”${base_dir}/full_`date +%F`”
inc_dir=”${base_dir}/`ls ${base_dir} | tail -1`”
cp -a ${base_dir} /data/backup/`date +”%F-%T”`_bak
innobackupex –defaults-file=/etc/my.cnf –user=root –apply-log –redo-only ${full_dir}
innobackupex –defaults-file=/etc/my.cnf –user=root –apply-log –redo-only ${full_dir} –incremental-dir=${inc_dir}

service mysqld stop
mv /data/mysql /data/mysql_bak`date +”%F_%T”`
mkdir /data/mysql
innobackupex –defaults-file=/etc/my.cnf –user=root –copy-back ${full_dir}
chown -R mysql.mysql /data/mysql
service mysqld start

###binlog日志还原
###这里选择的是最后一个binlog日志,如果增量备份的一个小时内,有多个mysql-bin.0000xx生成,需要手动选择bin-log日志;
###还原执行13:30点后的所有sql命令
Hour=`basename ${inc_dir}|grep -o ‘[0-9]*’`
binlog=`ls /data/bin_logs/|grep [0-9]|tail -1`
mysqlbinlog –start-datetime=”`date +%F` ${Hour}:30:00″ /data/mysql/${binlog} | mysql -uroot -p ‘123456’

###还原执行13:30-14:10之间的所有sql命令
##mysqlbinlog –start-datetime=”2018-11-21 13:30:00″ –stop-datetime=”2012-03-15 14:10:00″ /data1/log/mysql/mysql-bin.000001 > /tmp/mysql_restore_030915.sql
************************************************************

计划任务
************************************************************
10 00 * * * /bin/bash /root/.scripts/full_back.sh
30 * * * * /bin/bash /root/.scripts/inc_back.sh

如何实现MySQL数据库碎片处理及优化

mysql经过一段时间运行后,经过多次的DML操作会在表中留下很多碎片,造成数据存储时不连续,影响性能。下面几条语句可将mysql优化,建议不要高峰时段操作
#检查表
CHECK TABLE TABLE_NAME;
#分析表
ANALYZE TABLE TABLE_NAME;
#优化表
OPTIMIZE TABLE TABLE_NAME;
#修复表
REPAIR TABLE TABLE_NAME;

#例如对tb_user做优化处理
CHECK TABLE tb_user;
ANALYZE TABLE tb_user;
OPTIMIZE TABLE tb_user;
REPAIR TABLE tb_user;

#查询数据行数
SELECT
table_name,
table_rows
FROM
information_schema.tables
WHERE
table_schema = ‘myapp’
ORDER BY table_rows desc;

如何在Centos7安装Mariadb Galera Cluster三主集群

安装前准备:
禁用SELinux
/usr/sbin/setenforce 0
sestatus
安装vim wget lrzsz
yum install vim wget lrzsz -y
yum remove libperl
rm -rf /usr/lib64/perl5/CORE/libperl.so
更新操作系统
yum upgrade -y
reboot
设置hosts
vi /etc/hosts
192.168.1.36 mariadb1
192.168.1.48 mariadb2
192.168.1.51 mariadb3
一. 删除旧的mariadb 和  galera
1、yum方式安装的MySQL
yum remove mariadb galera mysql mysql-server mysql-libs compat-mysql51
查看是否还有mysql软件:
rpm -qa|grep mysql
如果存在的话,继续删除即可,删除方式:yum remove + 【名字】。
2、rpm方式安装的mysql
rpm -qa | grep -i mysql
MySQL-server-5.6.17-1.el6.i686
MySQL-client-5.6.17-1.el6.i686
卸载mysql
rpm -e MySQL-server-5.6.17-1.el6.i686
rpm -e MySQL-client-5.6.17-1.el6.i686
查看mysql服务
systemctl list-unit-files
3删除分散mysql文件夹
用whereis mysql 或者 find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/usr/lib64/mysql
/usr/share/mysql
清空相关mysql的所有目录以及文件
cd  /etc/selinux/targeted/active/modules/100/mysql ;rm -rf ./*;
cd  /var/lib/mysql;rm -rf ./*;
cd  /usr/lib64/mysql;rm -rf ./*;
cd  /usr/share/mysql;rm -rf ./*;
cd //etc/my.cnf.d/;rm -rf ./*;
rm -f /etc/my.cnf
二.添加MariaDB YUM repository
vi  /etc/yum.repos.d/MariaDB.repo
增加内容:
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.0/centos7-amd64
gpgkey=http://yum.mariadb.org/RPM-GPG-KEY-MariaDB
enabled=1
gpgcheck=1
三.用yum安装Percona XtraBackup(2.4.8版本 )
1.安装Percona repository
2.测试repository
yum list | grep percona
3.安装percona-xtrabackup-24
yum install percona-xtrabackup-24
四.用YUM命令安装MariaDB Galera Cluster
yum install  MariaDB-server  MariaDB-client  socat  -y
yum clean all
yum makecache
五.安装后启动节点配置相关账号
sudo systemctl start mariadb
或 操作系统没有systemctl命令时用下面这句命令执行
sudo /etc/init.d/mysql start
本地登录:
mysql -uroot -p
给root用户授权远程登录:
GRANT  all privileges on *.* to ‘root’@’%’ IDENTIFIED BY ‘123456’;
flush privileges;
新增galerauser 用户,并设置密码为1qaz2wsx :
insert into mysql.user(Host,User,Password) values(“localhost”,”galerauser”,password(“123456”));
insert into mysql.user(Host,User,Password) values(“%”,”galerauser”,password(“123456”));
GRANT  all privileges on *.* to ‘galerauser’@’%’ IDENTIFIED BY ‘123456’;
GRANT  all privileges on *.* to ‘galerauser’@’localhost’ IDENTIFIED BY ‘123456’;
flush privileges;
六.配置数据库账号
所有节点都执行下面这句命令
/usr/bin/mysql_secure_installation
七.配置集群配置文件
第一个节点(配置为启动节点)
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://’    #第一个启动节点配置
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.36′   #本机IP地址
wsrep_node_name=’mariadb1′    #集群节点名称
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
第二个节点:
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://192.168.1.36,192.168.1.51′
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.48′
wsrep_node_name=’mariadb2′
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
第三个节点:
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://192.168.1.36,192.168.1.48′
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.51′
wsrep_node_name=’mariadb_node3′
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
注意:如果要启动Mariadb Galera Cluster ,首先选择node1节点为初始节点,将node1作为第一个启动节点,并且设置gcomm为空,否则无法启动。
wsrep_cluster_address=’gcomm://’
八 .启动一个新的集群
根据不同版在主节点执行(第一次初始化时使用,只执行一次,以后不需要执行这一句):
/usr/sbin/mysqld –wsrep-new-cluster –user=mysql &
警告:对一个已存在的集群添加新的节点时,不要使用引导的方式(bootstrap)启动这个节点,不能在一个集群中使用两个引导节点。
其它节点执行:systemctl start mariadb
查看日志: tail -f /var/log/messages
本次启动顺序为:mariadb1–>>mariadb3–>mariadb2 关的顺序为: mariadb2–>>mariadb3–>mariadb1
重启节点命令为:systemctl restart mariadb
查看节点状态: systemctl start mariadb
第一个节点
第二个节点
第三个节点
九执行命令:
mysqld –wsrep_cluster_address=gcomm://192.168.1.36  # 也可以用DNS名称
注意:ip为第一个启动的那个主节点
十.关闭所有节点后重启集群
——–未完待续

如何在Centos7.4中修改yum安装的mariadb主-主-主集群datadir路径

1.查询当前数据库存储路径
# mysql -uroot -p
MariaDB [(none)]> show variables like ‘datadir%’;
#其中datadir项就是现存储的路径
#退出数据库登录状态
MariaDB [(none)]> exit;
2.停止数据库
# systemctl stop mariadb
3.新建mariadb新存储的路径文件夹
# mkdir -p /home/mysql/mysqldata
# mkdir -p /home/mysql/mysqllog/binlog
4.进入home目录,给新的路径文件夹mysql用户权限
# cd /home
# chown -R mysql:mysql mysql
# chmod 755 -R mysql
5.修改mysql服务启动脚本中的datadir路径
默认为 datadir=/var/lib/mysql 注释掉,更换为 datadir=/home/mysql/mysqldata
如果还有相同的/var/lib/mysql路径,同样修改为/home/mysql/mysqldata
# vi /etc/init.d/mysql
6.如果还是报错,可以偿试下,修改以下两个文件的参数ProtectSystem和ProtectHome为false
# vi /etc/systemd/system/multi-user.target.wants/mariadb.service
# vi /usr/lib/systemd/system/find
# vi /usr/share/mysql/systemd/mariadb.service
把ProtectSystem=full改成ProtectSystem=false
把ProtectHome=true改成ProtectHome=false
再执行下面这一句
# systemctl daemon-reload
7.修改/etc/my.cnf.d/server.cnf把datadir,socket和log-bin参数路径
# vi /etc/my.cnf.d/server.cnf
[mysqld]
port        = 3306
datadir = /home/mysql/mysqldata
socket =   /var/lib/mysql/mysql.sock
log-bin = /home/mysql/mysqllog/binlog/mysql-bin
#完整server.cnf配置文件如下所示:
[mysqld]
port        = 3306
datadir =  /home/mysql/mysqldata
socket =   /var/lib/mysql/mysql.sock
log-bin = /home/mysql/mysqllog/binlog/mysql-bin
collation-server = utf8_general_ci
init-connect=’SET NAMES utf8′
character-set-server = utf8
open_files_limit = 65535
log_bin_trust_function_creators = 1
skip-external-locking
back_log=750
skip-name-resolve
event_scheduler=1
lower_case_table_names = 1
innodb-defragment=1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
performance_schema=0
net_read_timeout=30
net_write_timeout=60
key_buffer_size = 384M
max_allowed_packet = 10M
table_open_cache = 512
sort_buffer_size = 6M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
expire_logs_days=3
myisam_sort_buffer_size = 16M
thread_cache_size = 32
join_buffer_size = 4M
bulk_insert_buffer_size = 16M
delay_key_write=ON
delayed_insert_limit=4000
delayed_insert_timeout=60
delayed_queue_size=4000
tmp_table_size=128M
max_heap_table_size=128M
max_connections=3000
max_connect_errors=30
interactive_timeout=600
wait_timeout=60
aria_pagecache_buffer_size=32M
#slow_query_log
long_query_time = 0.1
log_slow_verbosity=query_plan
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
innodb_file_per_table=1
innodb_fast_shutdown=0
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 132000M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = on
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_thread_concurrency=48
innodb_io_capacity=800
innodb_purge_threads=1
innodb_open_files=65535
innodb_read_io_threads = 8
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
thread_handling=pool-of-threads
thread_pool_oversubscribe=30
thread_pool_size=32
thread_pool_idle_timeout=60
thread_pool_max_threads=1000
[mysqldump]
quick
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 12M
read_buffer = 6M
write_buffer = 2M
[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
8.安全模式启动数据库后生成索引文件
执行到第6步,如果直接运行systemctl start mariadb命令,会报错:提示找不到mysql-bin.index文件;那是 修改了log-bin参数路径,由于权限问题,
执行下面命令:即用安全模式启动数据库后就会在/home/mysql/mysqllog/binlog/mysql-bin下生成索引文件
# /usr/bin/mysqld_safe  –user=mysql &
9.停掉mysql进程后
# pkill -u mysql
# ps -ef | grep mysql
# kill -9 mysql进程
9.复制/var/lib/mysql/* 到 /home/mysql/mysqldata下
# cd  /home/mysql/mysqldata
# rm -rf  *
# cp -rf /var/lib/mysql/* /home/mysql/mysqldata
# cd /home
# chown -R mysql:mysql mysql
# chmod -R 750 mysql
注意:原/var/lib/mysql数据文件夹还是保留,因为要用到socket=/var/lib/mysql/mysql.sock
10.重启mariadb服务
# systemctl  start  mariadb
11.打开另外一个节点,进入mysql当前控制台
# mysql -uroot -p123456
MariaDB [(none)]> SET GLOBAL wsrep_provider_options=’pc.bootstrap=YES’;
MariaDB [(none)]> show status like ‘wsrep%’;
12.再次重启mariadb服务
# systemctl  restart  mariadb
13.打开另外一个窗口,用来打印启动信息
# tail -f /var/log/messages
14.确认datadir是否改变
# mysql -uroot -p
MariaDB [(none)]> show variables like ‘datadir%’;
#其中datadir项就是现存储的路径
15.退出数据库登录状态
MariaDB [(none)]> exit;

如何在Windows上安装MarriaDB单机版

1.打开解压目录,然后找到my-large,my-medium,my-small,三个文件,根据你的电脑的 配置进行选择,我这里选择的是my-large文件。
右击编辑,编辑(黑色部分是需要自己添加的):
[client]
#password    =
port    = 3306
socket    = /tmp/mysql.sock
default-character-set=utf8
2.添加目录(这里的目录是你的解压目录):
[mysqld]
port    = 3306
socket    = /tmp/mysql.sock
basedir=E:/Softwear/mariadb-10.0.10-winx64
datadir=E:/Softwear/mariadb-10.0.10-winx64/data
default-character-set=utf8
default-storage-engine = InnoDB
3.在文件的最后在添加上:
[mysqlhotcopy]
interactive-timeout
[WinMySQLAdmin]
Server=E:\Softwear\mariadb-10.0.10-winx64\bin\mysqld.exe
单击文件——另存为——文件名为my.ini。将文件复制到C盘的system目录下即可。
4.在开始菜单输入cmd,右击以管理员身份运行,运行后将执行目录cd到你的问价解压目录中的bin目录中,例如我的解压在E:\Softwear\mariadb-10.0.10-winx64:
输入 e:   回车
输入 cd E:\Softwear\mariadb-10.0.10-winx64\bin  回车即可跳转到bin目录。
5.完成上面的步骤之后就可以开始安装服务和启动服务了。
输入 mysqld.exe –install MariaDB
等待成功后,输入 net start MariaDB 即可启动服务开始你的MariaDB之旅了。
顺便提一句,删除的时候也很简单,输入 mysqld.exe –remove MariaDB即可
 
6.修改密码和远程授权访问
mysql -uroot -p
输入空密码
 use mysql;
update user set password=PASSWORD(“123456″) where user=”root”;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
flush privileges;