grant select on database_name.* to 'user'@'%' identified by 'password';
mysql create read only user
Leave a reply
Tag Archives: MySQL
mysql master slave replication
log-bin=mysql-bin
server-id=1
innobackupex --user=root --password=password /path/to/backupdir
innobackupex --user=root --password=password --apply-log /path/to/backupdir/$TIMESTAMP/
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'remote_ip' IDENTIFIED BY 'slave_password';
server-id=2
cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 100
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=100;
START SLAVE;
mysql> SHOW SLAVE STATUS \G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 13
Adding another slave, run on slave:
innobackupex --user=root --password=password --slave-info /path/to/backupdir
innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/
On master:
GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'new_slave_ip' IDENTIFIED BY 'new_slave_pass';
cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000001 100
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl2', MASTER_PASSWORD='new_slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=100;
START SLAVE;
skip-slave-start
server-id=3
Use sshfs or rsync to move data
mysql /etc/init.d restart not working
/etc/init.d/mysql does not start mysqld at system start up on Ubuntu
grep debian-sys-maint /etc/mysql/debian.cnf
ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
mysql> UPDATE mysql.user SET Password = password(‘password’) WHERE User = ‘debian-sys-maint’;
service mysql restart
check binlog enabled mysql
SHOW VARIABLES LIKE ‘log_bin’;
Google datastudio connect MySQL 8 problem
Workaround is tu use proxysql
mysql_query_rules:
{
rule_id=1
active=1
match_pattern="\@\@query_cache_size"
replace_pattern="null"
apply=0
},
{
rule_id=2
active=1
match_pattern="\@\@query_cache_type"
replace_pattern="null"
apply=0
},
{
rule_id=3
active=1
match_pattern="\@\@tx_isolation"
replace_pattern="null"
apply=0
},
find mysql sock
mysqladmin variables | grep socket
from caching_sha2_password back to mysql_native_password
[mysqld]
default_authentication_plugin=mysql_native_password
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.01 sec)
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql_secure_installation
Securing the MySQL server deployment.
centos 7 mysql 5.7
yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm -y
yum repolist enabled | grep “mysql.*-community.*”
mysql-connectors-community/x86_64 MySQL Connectors Community 165
mysql-tools-community/x86_64 MySQL Tools Community 115
mysql57-community/x86_64 MySQL 5.7 Community Server 444
systemctl start mysqld
grep ‘temporary password’ /var/log/mysqld.log
2020-08-08T18:56:50.367913Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxxxxx
mysql 5.7 install ubuntu 16.04
apt-get install mysql-server
mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
mysqldump: Got error: 1033: Incorrect information in file: ‘./www_111/tblactivitylog.frm’ when using LOCK TABLES
Its possible your database files was restored on wrong server and wrong MySQL version, maybe too old MySQL server.
ERROR 3 (HY000) at line 25: Error writing file ‘./somedb/my_table1.frm’ (Errcode: 122)
If you have issues to import a database using Virtualmin (Webmin) you need to disable or increase quotas:
Total server quota: Unlimited
Server administrator’s quota: Unlimited
By default it was set limit, so that error means: Disk quota exceeded
select no cache
mysql> SELECT SQL_NO_CACHE * from some_tabe limit 10;
using index condition optimizer_switch
Index Condition Pushdown Optimization
MariaDB [(none)]> SET optimizer_switch = 'index_condition_pushdown=off';
vi /etc/my.cnf.d/server.cnf
optimizer_switch=index_condition_pushdown=off
To check:
mysql -e 'show global variables like "%optimizer_switch%";' | grep index_condition_pushdown