When trying to restore a database backup to Managed DigitialOcean MySQL 8 database, i get following error root@ocp:~# mysql -u doadmin -p’BKwsQcqEGbSV3w’ -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 3750 (HY000) at line 223: Unable to create or change a table ... Read more
When restoring a MySQL database, i get error root@server:~# mysql production < db.sql ERROR 1118 (42000) at line 733: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. root@server:~# I installed exactly same MraiaDB version on both … Read more
After upgrading MariaDB on Ubuntu server, i got following error root@server:~# mysql ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded root@server:~# Users created worked fine. Only root user had this error. To fix, you need to enable auth_socket.so plugin. Edit file vi /etc/mysql/mariadb.conf.d/50-server.cnf Find [mysqld] Add below plugin-load-add = auth_socket.so Resatrt MariaDB systemctl restart mysqld … Read more
To initialize the MySQL data directory, run This will create /var/lib/mysql directory. For some versions of MySQL you can use Example See MySQL
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 … Read more
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 ... Read more
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 … Read more
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. To change a user to use mysql_native_password, run
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] … Read more
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 ... Read more