MySQL Archives - Page 8 of 8

MySQL root can’t login to phpMyAdmin

On Debian/Ubuntu MySQL root user can only login using socket. This is done with Plugin.

MariaDB [(none)]> select User, Plugin from mysql.user;
+-----------+-------------+
| User      | Plugin      |
+-----------+-------------+
| root      | unix_socket |
| sserverok |             |
+-----------+-------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

To fix this, first set a password for user root.

Change MySQL root password

To disable plugin, run following commands in MySQL command prompt as user root.

update mysql.user set Plugin='' where User="root";
flush privileges;

MySQL
phpMyAdmin

January 19, 2018

MySQL Optimize

MySQL is one of the most popular relational database management systems in use today, powering many popular websites and applications. To get the best performance, you need to optimize the MySQL database.

innodb_buffer_pool_size – Increase the buffer pool size. The buffer pool is where MySQL stores data and indexes in memory, so increasing its size can improve performance. A good rule of thumb is to set the buffer pool size to 80-90% of available memory.

nnodb_buffer_pool_instances – Increase the innodb_buffer_pool_instances. This will help to reduce contention and increase scalability.

innodb_log_file_size – Increase the innodb_log_file_size. This controls the size of the InnoDB redo logs, which are used to recover the database in case of a crash.

query_cache_size – Increase the query_cache_size. This cache stores the results of SELECT statements, so increasing its size can improve performance for frequently-run queries.

Tune the sort_buffer_size, read_buffer_size and read_rnd_buffer_size based on your workload.

Optimize your database schema by creating indexes on columns that are frequently searched.

Optimize MariaDB

Edit file

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Find

[mysqld]

On this section, you will see “#skip-external-locking”, add below.

max_connections        = 500
wait_timeout = 8800
interactive_timeout = 8800
query_cache_size=0
query_cache_type=0
join_buffer_size=2048K
tmp_table_size=256M
max_heap_table_size=256M
table_open_cache=4000
table_definition_cache=800
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10
innodb_log_file_size=1G
performance_schema = ON
skip-name-resolve=1

These are some basic settings. Now run the MySQL tuner, and make suggestions as required.

To make the settings live, you need to restart MariaDB server.

systemctl restart mariadb

MySQL Tuner

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

innodb_buffer_pool_size

On a server with 32 GB RAM, set

innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=20

Having more innodb_buffer_pool_size, make MySQL act like an in memory database.

innodb_buffer_pool_instances divide buffer pool into smaller instances. It is better use 1 GB per instance.

To see current innodb_buffer_pool values, run

show variables like '%innodb_buffer_pool%';

innodb_flush_log_at_trx_commit

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

If you can afford to lose 1 second of data, setting value of innodb_flush_log_at_trx_commit to 2 will improve performance. For most web applications, this works fine.

innodb_flush_log_at_trx_commit=2

Slow Query Summary over 12 hours.

pt-query-digest --since=24h /var/log/mysql/mysql-slow.log --limit=100% --filter '($event->{user} || \"\") =~ \"USER_HERE\"'

See MySQL

January 12, 2018

MySQL

MySQL Installation

MySQL User Management

MySQL Optimize

MySQL Backup/Restore

MYSQL Commands

How to rename a MySQL database table

MySQL Utils

Auto Restart MySQL if Crashed

MySQL GUI

Dbeaver.io – Support Windows, Linux and Mac.
https://www.sequelpro.com – GUI Application for MAC.
MySQL Workbench – Linux, Windows, MAC
https://www.heidisql.com
https://dbgate.org
https://www.adminer.org
https://www.phpmyadmin.net

MySQL Errors

January 11, 2018

Reset MySQL root Password

To reset MySQL root password, stop MySQL

service mysql stop

Start MySQL with

mysqld_safe --skip-grant-tables

Start another terminal, login to MySQL as root

mysql -u root

Run following commands to change password.

update mysql.user set password=PASSWORD("PASSWORD_HERE") where User='root';
flush privileges;
quit

Now you need to kill running MySQL processes and start MySQL as normal.

killall mysqld_safe
killall mysqld

Restart MySQL

service mysql start

See MySQL Password Reset/Change

January 11, 2018

MySQL User

Create a user

For local mysql access

CREATE USER 'USERNAME-HERE'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'localhost';
FLUSH PRIVILEGES;

To allow remote access, run

CREATE USER 'USERNAME-HERE'@'%' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME-HERE'@'%';
FLUSH PRIVILEGES;

To allow a user to create another user

GRANT GRANT OPTION ON *.* TO 'USERNAME-HERE'@'localhost';

Now this user will be able to create new users.

Create a user with root privilages

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'MYSQL_PASSWORD' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;

For MySQL 8

CREATE USER 'admin'@'%' IDENTIFIED BY 'AEs308SuEtT0Hs';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

December 21, 2017

Debian Allow MySQL root user to login using phpMyAdmin

On Debian/Ubuntu by default MySQL root user can only login from local accounts.

MariaDB [(none)]> select plugin from mysql.user where user='root';
+-------------+
| plugin      |
+-------------+
| unix_socket |
+-------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

To enable login from phpMyAdmin, you need to set plugin to “mysql_native_password”.

To do this, login to MySQL as user root, then run

update mysql.user set plugin='mysql_native_password' where user='root';

December 17, 2017

Configuring MySQL .my.cnf

vi ~/.my.cnf

Add following content

[client]
user = root
password = "DBPASSWORD"
 
[mysqladmin]
user = root
password = "DBPASSWORD"
 
[mysqldump]
user = root
password = "DBPASSWORD"

Replace DBPASSWORD with your MySQL root password.

Now set permission for this file to 600, so no user other than root can see it.

chmod 600 ~/.my.cnf

December 17, 2017

InnoDB: space header page consists of zero bytes in tablespace

On a server, starting MySQL i get following error

[root@3blogger log]# cat mysqld.log 
170102 21:34:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-01-02 21:34:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-02 21:34:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2) starting as process 8048 ...
2017-01-02 21:34:02 8048 [Note] Plugin 'FEDERATED' is disabled.
2017-01-02 21:34:02 8048 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-01-02 21:34:02 8048 [Note] InnoDB: The InnoDB memory heap is disabled
2017-01-02 21:34:02 8048 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-01-02 21:34:02 8048 [Note] InnoDB: Memory barrier is not used
2017-01-02 21:34:02 8048 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-01-02 21:34:02 8048 [Note] InnoDB: Using Linux native AIO
2017-01-02 21:34:02 8048 [Note] InnoDB: Using CPU crc32 instructions
2017-01-02 21:34:02 8048 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-01-02 21:34:02 8048 [Note] InnoDB: Completed initialization of buffer pool
2017-01-02 21:34:02 8048 [Note] InnoDB: Highest supported file format is Barracuda.
2017-01-02 21:34:02 8048 [Note] InnoDB: Log scan progressed past the checkpoint lsn 644142666552
2017-01-02 21:34:02 8048 [Note] InnoDB: Database was not shutdown normally!
2017-01-02 21:34:02 8048 [Note] InnoDB: Starting crash recovery.
2017-01-02 21:34:02 8048 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-01-02 21:34:02 8048 [ERROR] InnoDB: space header page consists of zero bytes in tablespace ./3blogger/wp_wfNet404s.ibd (table 3blogger/wp_wfNet404s)
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:1024 Pages to analyze:64
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 1024, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:2048 Pages to analyze:32
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 2048, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:4096 Pages to analyze:16
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 4096, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:8192 Pages to analyze:8
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 8192, Possible space_id count:0
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:16384 Pages to analyze:4
2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 16384, Possible space_id count:0
2017-01-02 21:34:02 7fcef14617e0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./3blogger/wp_wfNet404s.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
170102 21:34:02 mysqld_safe mysqld from pid file /var/lib/mysql/3blogger.3blogger.com.pid ended
[root@3blogger log]#

This was because the server used InnoDB database engine and my.cnf was not configured properly.

First i removed the log files.

mkdir ~/mysql-problem-2017-01-03
mv /var/lib/mysql/ib_* ~/mysql-problem-2017-01-03

Now i edit /etc/my.cnf as follows

[root@3blogger etc]# cat /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port        = 3306
socket=/var/lib/mysql/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@3blogger etc]#

This MySQL config is based on /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf provided my MySQL.

[root@server12 ~]# rpm -q --filesbypkg mysql-server-5.1.73-7.el6.x86_64 | grep .cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-huge.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-large.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-medium.cnf
mysql-server              /usr/share/doc/mysql-server-5.1.73/my-small.cnf
mysql-server              /usr/share/mysql/my-huge.cnf
mysql-server              /usr/share/mysql/my-innodb-heavy-4G.cnf
mysql-server              /usr/share/mysql/my-large.cnf
mysql-server              /usr/share/mysql/my-medium.cnf
mysql-server              /usr/share/mysql/my-small.cnf
[root@server12 ~]#

You can also see this default MySQL InnoDB Heavy Config at

https://github.com/twitter/mysql/blob/master/support-files/my-innodb-heavy-4G.cnf.sh

Now i started MySQL, everything worked fine.

See mysql

January 3, 2017

myisamchk: Argument list too long

When repairing a large database, i got following error

[root@server root]# myisamchk --silent --force --fast --update-state \
          --key_buffer_size=64M --sort_buffer_size=64M \
          --read_buffer_size=1M --write_buffer_size=1M /var/lib/mysql/databasename/*.MYI
-bash: /usr/bin/myisamchk: Argument list too long

To fix the error, run

SOLUTION 1

find /backup/mysql/bizhat_s2 -type f -print0 -name '*.MYI' | xargs -0 myisamchk --force --fast --update-state --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M

SOLUTION 2

find /var/lib/mysql/databasename -name *.MYI -exec myisamchk -r {} \;

See MySQL

March 29, 2009

Category: MySQL

Optimize MariaDB

innodb_flush_log_at_trx_commit

MySQL Installation

MySQL User Management

MySQL Optimize

MySQL Backup/Restore

MYSQL Commands

MySQL Utils

MySQL GUI

MySQL Errors

Create a user

To allow a user to create another user

Create a user with root privilages

SOLUTION 1

SOLUTION 2