Run the following query as root:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema
Let’s install the dependencies first:
yum install mariadb-server mariadb-embedded mariadb-devel httpd php php-gd php-bcmath php-ctype php-xml php-xmlreader php-xmlwriter php-session php-mbstring php-ldap php-gettext php-mysqli pcre pcre-devel libevent libevent-devel libssh2 libcurl libcurl-devel net-snmp-devel net-snmp-agent net-snmp-libs libxml2 libxml2-devel
Edit /etc/php.ini file and change this variables to:
max_execution_time = 600
max_input_time = 600
memory_limit = 256M
post_max_size = 32M
upload_max_filesize = 16M
date.timezone = Europe/Rome
Continue reading “Install Zabbix 3.4 server on CentOS 7”
What to do when:
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
Add “–single-transaction” to your mysqldump string:
$MYSQLDUMP --single-transaction -u $MUSER -h $MHOST -p"$MPASS" $db | $GZIP -9 > $FILE
$MYSQLDUMP is the path of mysqldump executable.
So next time i’ll not forget!
1) STOP MySQL service
2) Upgrade with pkg_add, portupgrade, manually, ecc.
3) Start MySQL service
4) And remember to run
mysql_upgrade -u root -p
This is the (bash) script that i use for backup all MySQL databases and other important stuffs like etc/ dir, configuration files in /usr/local/etc and whatever you want.
As you can see, it’s launched 3 times a week by cron, on sunday, wednesday and friday at 23:55. It create a list of installed packages, dump all MySQL databases, sync important files and dirs with rsync and finally create a tar.gz of backupped files.
It would be interesting add one cool thing, after tar.gz is done it could be copied via scp to another host (and it would be a better backup :P).
Actually i haven’t a pc running 24/7 (old good OpenBSD gateway, i miss you!!) and i download manually the tar.gz the morning after (shame on me) :P
NB: MySQL backup part is partially taken from a GREAT MySQL backup script and adapted to my needs. Script is from bash.cybercity.biz
Continue reading “Backup all MySQL databases and other important stuff on FreeBSD”