I am able to log in via mysql -u myuser -p mydb -h localhost with this:
grant all privileges on mydb.* to myuser@'%' identified by
'1234567890123456789012345678901234567890123456789012345678901234567890123456789';
But not after I do this:
grant all privileges on mydb.* to myuser@'%' identified by
'12345678901234567890123456789012345678901234567890123456789012345678901234567890';
Where is this hard limit of 79 characters for a database password coming from?
I just installed the mariadb in my ubuntu 19.10 by sudo apt install mariadb-server mariadb-client after when I am trying to start the server by sudo systemctl start mariadb.service shell get freeze I have to use Ctl+c to get shell running.
In the status it's showing activating(start)
~$ sudo systemctl status mariadb.service
mariadb.service - MariaDB 10.3.22 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: activating (start) since Mon 2020-04-20 08:44:57 IST; 5min ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 10705 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 10707 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 10711 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSRE
Main PID: 10759 (mysqld)
Tasks: 15 (limit: 4915)
Memory: 64.6M
CGroup: /system.slice/mariadb.service
└─10759 /usr/sbin/mysqld
Apr 20 08:44:57 TheCybertron systemd[1]: Starting MariaDB 10.3.22 database server...
Apr 20 08:44:57 TheCybertron mysqld[10759]: 2020-04-20 8:44:57 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0ubuntu0.19.10.1) starting as process 10759 ...
I have successfully increased the nofile and nproc value for the local users, but I couldn't find a proper solution for the processes launched by systemd. Adding max_open_files to the MariaDB configuration doesn't help. su - mysql to change the limit manually doesn't work either (This account is currently not available).
/etc/security/limits.conf
* soft nofile 102400
* hard nofile 102400
* soft nproc 10240
* hard nproc 10240
/etc/security/limits.d/20-nproc.conf (no other files present in the directory)
* soft nofile 102400
* hard nofile 102400
* soft nproc 10240
* hard nproc 10240
/etc/sysctl.conf
fs.file-max = 2097152
/etc/pam.d/system-auth
#%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth required pam_env.so
auth sufficient pam_unix.so nullok try_first_pass
auth requisite pam_succeed_if.so uid >= 1000 quiet_success
auth required pam_deny.so
account required pam_unix.so
account sufficient pam_localuser.so
account sufficient pam_succeed_if.so uid < 1000 quiet
account required pam_permit.so
password requisite pam_pwquality.so try_first_pass local_users_only retry=3 authtok_type=
password sufficient pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password required pam_deny.so
session optional pam_keyinit.so revoke
session required pam_limits.so
-session optional pam_systemd.so
session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session required pam_unix.so
/etc/pam.d/systemd-user
#%PAM-1.0
# Used by systemd when launching systemd user instances.
account include system-auth
session include system-auth
auth required pam_deny.so
password required pam_deny.so
/var/log/mariadb/mariadb.log
[Warning] Changed limits: max_open_files: 1024 max_connections: 32 table_cache: 491
/proc/mysql_pid/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 30216 30216 processes
Max open files 1024 4096 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 30216 30216 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
It is interesting that different processes (users) have different Max open files number:
mysql - 1024 4096
apache - 1024 4096
postfix - 4096 4096
I'm running Mysql in production today but are planning to switch to either MariaDB or Percona server when I move to new servers. The main reason for the switch is to distance us from Oracle, but also to get better performance and new features.
It looks like Percona server and MariaDB is pretty similar, and they also promote each other's solutions. What would you say is the main difference between them?
I'm running MySQL for most of our DBs right now, and would like to get off the Oracle ship before it takes more of a turn. My understanding is that the transition from MySQL->MariaDB should be trivial, but going the other way is not so easy. Has anyone made this jump yet?
Edit: Bonus points if you have anecdotal information on doing this with Debian/Ubuntu.