To change MySQL database char set and collation, run
ALTER DATABASE DB_NAME_HERE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
To usee current char set and collation, run
USE DB_NAME_HERE; SELECT @@character_set_database, @@collation_database;
You can specify char set when creating database with following syntax
CREATE DATABASE DB_NAME_HERE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
On an Apache server, MySQL can’t connect when you use localhost, but it work when you chane to IP address 127.0.0.1
When you use “localhost”, it use socket for connecting to MySQL server, this is faster than using TCP/IP connection, that is used when you use IP address to connect to MySQL server.
First find out socket path. To do this login to MySQL server, run
show variables like "socket";
See if you can connect using this socket with command
mysql -S /var/lib/mysql/mysql.sock -u root -p
In this cause, login to MySQL using socket worked.
I created a simple test PHP script to verify MySQL connection, it was able to connect to MySQL server using “localhost”.
Same script did not work when i try access it using web server. So the problem is web server user not able to connect to MySQL socket. You need to check permission for socket and parrent folders. In this case problem is fixed by running
chmod 755 /var/lib/mysql/You can verify enabling SSH access for web server user, then connect to MySQL using command line or try access socket file as apache user.
MySQL Socket Path in php.ini
When a PHP application use localhost to connect, PHP find location of socket from php.ini, you need to verify this path set in php.ini is same as the socket path used by MySQL server.
# cat /etc/php.ini | grep socket ; Default timeout for socket based streams (seconds) ; http://php.net/default-socket-timeout default_socket_timeout = 60 ;extension=php_sockets.dll ; Default socket name for local MySQL connects. If empty, uses the built-in ; http://php.net/pdo_mysql.default-socket pdo_mysql.default_socket= /var/lib/mysql/mysql.sock ; Default socket name for local MySQL connects. If empty, uses the built-in ; http://php.net/mysql.default-socket mysql.default_socket = /var/lib/mysql/mysql.sock ; Default socket name for local MySQL connects. If empty, uses the built-in ; http://php.net/mysqli.default-socket mysqli.default_socket = /var/lib/mysql/mysql.sock #If path is differnt, you need to make it same. You can either modify php.ini or MySQL server config file.
To install LAMP (Apache, MySQL, PHP) on Ubuntu/Debian web server, run
apt-get install lamp-server^
This use meta package install LAMP. If you want to remove, don’t remove the meta package as it will remove many other required packages. You need to remove packages one by one.
Related Posts
To Find MySQL root password on EasyEngine, run
cd /opt/easyengine/services && docker-compose exec global-db bash -c 'echo $MYSQL_ROOT_PASSWORD'
Or
cat /opt/easyengine/services/docker-compose.yml | grep MYSQL_ROOT_PASSWORD | awk -F'=' '{print $2}'
cat /opt/easyengine/services/docker-compose.yml | grep MYSQL_ROOT_PASSWORD | cut -d'=' -f2
To connect to MySQL console, run
cd /opt/easyengine/services && docker-compose exec global-db bash -c 'mysql -uroot -p${MYSQL_ROOT_PASSWORD}'
See EasyEngine
When restoring MySQL backup taken on Amazon RDS, i get error
root@PRD-50ml:~# mysql sok_db2 < db.sql ERROR 1193 (HY000) at line 24: Unknown system variable 'GTID_PURGED' root@PRD-50ml:~#
To fix this, open db.sql in text editor, remove the line
SET @@GLOBAL.GTID_PURGED='';
Another solution is take a new MySQL backup with --set-gtid-purged=OFF option and restore it.
mysqldump -u DB_USER -p --set-gtid-purged=OFF --triggers --routines --events DB_NAME > DB_NAME.sql
To create a database, you can use
create database DB_NAME;
Example
To create a database with specific charset use
create database DB_NAME character set utf8mb4 collate utf8mb4_bin
Or
create database DB_NAME character set utf8mb4 collate utf8mb4_unicode_ci;
You can use whatever character set you wish instead of utf8mb4.
Example
create database serverok_db2 character set utf8mb4 collate utf8mb4_unicode_ci;
To delete a database, use
drop database DB_NAME;
In VestaCP server, MySQL root password is stored in file /usr/local/vesta/conf/mysql.conf, to find MySQL root password, run
cat /usr/local/vesta/conf/mysql.conf
MySQL 8 use caching_sha2_password as the default authentication method. Many MySQL clients still do not support this method. If you need to use the old method, you can set the authentication plugin as mysql_native_password. You can also set this as the default method by editing my.cnf file.
[mysqld]
default_authentication_plugin=mysql_native_passwordTo change a user to use mysql_native_password, run
ALTER USER 'USERNAME'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'PASSWORD_HERE';if you have cloud sevrer with less disk space, it is better disable MySQL bin log as it take approx 3 GB of disk space on bitnami server.
To disable MySQL binlog, edit mysql config file.
vi /opt/bitnami/mysql/my.cnf
Find
[mysqld]
Add below
disable_log_bin
Here is what i have in the my.cnf
root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf [mysqladmin] user=root [mysqld] disable_log_bin basedir=/opt/bitnami/mysql datadir="/opt/bitnami/mysql/data" port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock tmpdir=/opt/bitnami/mysql/tmp max_allowed_packet=32M bind-address=127.0.0.1 skip-name-resolve=1 default-authentication-plugin=mysql_native_password character-set-server=UTF8 collation-server=utf8_general_ci [client] port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock default-character-set=UTF8 [manager] port=3306 socket=/opt/bitnami/mysql/tmp/mysql.sock pid-file=/opt/bitnami/mysql/tmp/manager.pid default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin !include /opt/bitnami/mysql/bitnami/my.cnf root@wordpress-vm:~#
Now restart MySQL
/opt/bitnami/ctlscript.sh stop mysql /opt/bitnami/ctlscript.sh start mysql
You can now remove the file starting with binlog from folder /opt/bitnami/mysql/data
rm -f /opt/bitnami/mysql/data/binlog.*
When restoring a MySQL database, i get error “MySQL server has gone away”.
# mysql -u root -p'serverok123' sok_wp < backup.sql ERROR 2006 (HY000) at line 5095: MySQL server has gone away #
To fix this, edit MySQL configuration file. Add following line under [mysqld] section.
max_allowed_packet = 256M
Restart MySQL
systemctl restart mysql
If you use MariaDB
systemctl restart mariadb
Related Posts
When starting MaraDB, i got error
root@localhost:/var/log/mysql# cat error.log 2020-01-19 6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds 2020-01-19 6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control' 2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' init function returned error. 2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed. 2020-01-19 6:19:17 0 [Note] InnoDB: Using Linux native AIO 2020-01-19 6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-01-19 6:19:17 0 [Note] InnoDB: Uses event mutexes 2020-01-19 6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-01-19 6:19:17 0 [Note] InnoDB: Number of pools: 1 2020-01-19 6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-01-19 6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2020-01-19 6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool 2020-01-19 6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-01-19 6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301 2020-01-19 6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-01-19 6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-01-19 6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-01-19 6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-01-19 6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-01-19 6:19:17 0 [Note] InnoDB: Waiting for purge to start 2020-01-19 6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296 2020-01-19 6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-01-19 6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables 2020-01-19 6:19:17 0 [ERROR] Aborting root@localhost:/var/log/mysql#
To fix error, remove the file
mv /var/lib/mysql/aria_log_control /root/
On my computer running Ubuntu 18.04, MriaDB stopped working. PHP application i run on my computer failed with error
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ok_test.feeds' doesn't exist (SQL: select * from `feeds` where `processed` = 0)
Application can’t find the table. So i tried to login to MySQL and see if table is there or not. But i get error
boby@sok-01:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2504 Server version: 10.2.30-MariaDB-1:10.2.30+maria~bionic-log mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 "Too many open files") MariaDB [(none)]>
Next i checked if this is due to any MySQL upgrade. I found MySQL got updated today
root@sok-01:~# grep mariadb /var/log/dpkg.log 2019-12-13 08:02:46 status triggers-pending mariadb-server-10.2:amd64 1:10.2.29+maria~bionic 2019-12-13 08:02:46 upgrade mariadb-common:all 1:10.2.29+maria~bionic 1:10.2.30+maria~bionic 2019-12-13 08:02:46 status half-configured mariadb-common:all 1:10.2.29+maria~bionic 2019-12-13 08:02:46 status unpacked mariadb-common:all 1:10.2.29+maria~bionic 2019-12-13 08:02:46 status half-installed mariadb-common:all 1:10.2.29+maria~bionic
To fix this error, run
systemctl edit mysql
This wil open a text editor. Add following
[Service] LimitNOFILE=8192
Save and quit the editor. Restart MaraDB with
systemctl restart mysql
MySQL will work properly now. systemctl edit mysql will create file /etc/systemd/system/mysql.service.d/override.conf
root@sok-01:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=8192 root@sok-01:~#