MySQL
This FAQ page collects operational notes for MySQL.
MySQL replication is broken with <nowiki>"Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave."</nowiki>, how can I fix it?
MySQL multimaster replication needs some advanced MySQL skills. If you do not have them, it is better if you refer to MiRTA PBX support and subscribe a "Nagios monitoring and OS management" service. If instead you have the needed knowledge, understanding that a wrong action can get things worst and you may lose data, to recover from this problem you need:
- Get the MySQL slave status with "show slave status"
- Stop the slave replication with "stop slave"
- Reset the relay log with "reset slave"
- Reinitialize the relay log with "change master to master_log_file='<Relay_Master_Log_File>', master_log_pos=<Exec_Master_Log_Pos>"
- Start the slave replication with "start slave"
I have lost the me_media table
It is easy to panic and to abruptly remove the big voipmonitor.me_media table who is filling your disk, but now you need to recreate it and maybe reduce the retention period for voipmonitor data file. I have created a script as protected/the related application page doing this job.
MySQL time is wrong
It is possible your mysql has no more valid timezone informations. Please run the following command:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysqlI need to skip lots of voipmonitor add partition collisions
Why don't use a script like
while [ 1 ]; do if [ `echo "show slave status for channel 'from_voip3_to_voip1'" | mysql -u root -ppassw0rd | grep voipmonitor | wc -l` -gt 0 ]; then echo "stop slave for channel 'from_voip3_to_voip1'; set global sql_slave_skip_counter=1; start slave for channel 'from_voip3_to_voip1'" | mysql -u root -ppassw0rd; fi; sleep 5s; echo "show slave status for channel 'from_voip3_to_voip1'\G" | mysql -u root -ppassw0rd | grep Seconds_Behind_Master; done
MySQL max open file limit is 5000
The standard MySQL install has an open file limit of 5000 files. To raise this limit, edit the file /usr/lib/systemd/system/mysqld.service and set like
LimitNOFILE = 32768
then reload the service with:
systemctl daemon-reload
service mysqld restart
If instead you still run CentOS 6, you can add the following to my.cnf and restart mysqld
open_files_limit = 32768
I need to connect to MySQL using SSL
From version 4.0.13 is possible to connect to MySQL using SSL. A small change is needed on both /var/www/html/pbx/include/db.the related application page and /var/lib/asterisk/agi-bin/include/db.the related application page adding the following rows in the right place:
$dbconn=NewADOConnection($dbtype);
$dbconn->ssl_capath="";
$dbconn->clientFlags=MYSQLI_CLIENT_SSL;
$GLOBALS['ADODB_SESS_CONN']=$dbconn;
I need to load timezone data in MySQL
You can do from your database server running:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p MySQL
Where do I find MySQL 5.5 ?
Use this command:
yum install http://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-release-el7-5.noarch.rpm
How fast should be MySQL?
It is not easy to measure the performance of MySQL, but there is a script you can try to run in /var/www/html/pbx/protected
These are some performance analysis, just to compare
Checking voicemail table
Collected 1269 queries
Running 100 cycles
Time required: 11.420516967773
Time for 1000 requests: 8.9996193599475
Checking sipfriends table
Collected 256 queries
Running 100 cycles
Time required: 3.5946228504181
Time for 1000 requests: 14.041495509446
Checking st_states table
Loading 100000 queries
Time required: 14.124006986618
Time for 1000 requests: 0.14124006986618
I can't upgrade MySQL, the GPG key is expired or invalid
On EL10, I had to do this:
rpm -e gpg-pubkey-a8d3785c-6536acda
curl -fsSL "https://keyserver.ubuntu.com/pks/lookup?op=get&search=0xB7B3B788A8D3785C" -o /tmp/fresh.asc
gpg --import /tmp/fresh.asc
\rm /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023
gpg --output /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 --export BCA43417C3B485DD128EC6D4B7B3B788A8D3785C
I get an error "ERROR 1118 (42000) at line 25: 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."
That because MySQL is exagerating his needs. Disable it with
SET GLOBAL innodb_strict_mode = OFF;