New answers tagged

1

It seems an AppArmor related problem. Try disabling AppArmor issuing systemctl stop apparmor.service and restart MariaDB Anyway, as already suggested, /home is the wrong place for MariaDB/MySQL. Please move your data directory under /var/lib


1

You are using a wrong directory for datadir. You should not store MySQL/MariaDB's data under /home. For security reasons the database is not allowed to access user home directories. In order to accomplish this, systemd runs MariaDB in a container, which cannot access /home. The "standard" place for the datadir is /var/lib/mysql. Change the datadir to the ...


3

What you are asking is a very loaded question. Why ? You are not looking at this the wrong way. I wish more people asked this type of question. Believe it or not, there are rare circumstances where older versions of MySQL can outperform newer versions of MySQL. When ? In a single threaded environment. I wrote about this years ago where I quote from MySQL ...


2

There is absolutely no impact to the master's operations whether there is active replication or not, except of course for the load created by the replication process itself. To be 100% clear, the master's transactions do not depend on its replication and therefore will not perform differently. The only two actual risks of stopping the replication process ...


1

No, it is not normal. It is caused by the bug Excessive memory used in memory/innodb/os0file starting 5.7.8. It is tracked in Ubuntu as Excessive consumption RAM of mysqld daemon in Ubuntu 16.04, and there is also a discussion on Reddit.


0

I use mariadb 10 under Centos 7 linux. It work well because by default database stores every table per file and indexes per other file. Check that you create indexes for fields which are used in "where" and "join on" clauses. The right choice of fields for indexes may give you raise performance more than 100 times. How to install mariadb 10 on Centos 7 you ...


0

How about using a framework for logging. Log4PHP would also improve your logging over all. It has a MySQL appender. https://logging.apache.org/log4php/docs/appenders/pdo.html


0

This kind of looks like something important got removed with the package installation. the errors relate to policy kit. Can you confirm that polkit and dbus are both still installed? rpm -qi dbusand rpm -qi polkit logs may also contain information but seems here a policy is preventing the start of these services due to dbus not responding...because dbus is ...


1

This happens because by default, OpenSSL tries to verify the certificate chain for your certificate. Your certificate is self-signed, that is, no other entity has verified it, and that is why certificate verification fails in OpenSSL. In order to disable certificate verification, you need to add MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag to the ...


0

InnoDB/MySQL can't allocate 1G for some reason. At the same time, free -h shows some free memory. That means that something inside mysql or outside of mysql allocating memory. The my.cnf above does not show any additional memory allocation, but you have this include: "!includedir /etc/mysql/conf.d/" Please check the content of this directory and see if there ...


0

For those who still having this issue, you can follow this way to do it, I found this answer in a website. It helps me a lot: shell> mysql -u username -p -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'" | tail -n+2 >> ...


0

Named timezones are usable when they exist in your mysql database. In your case it seems that you don't have them there, so you can use offset relatives to UTC e.g: '+07:00' or '-04:00' or '+00:00'.


0

You can include variables files at run-time. For instance you could have: /<ansible-root>/environments/ /env-A.yml /env-B.yml These files would contain the site-specific variables, like: --- variable_a: foo variable_b: bar ...which you would reference from your plays with the standard ({{ ...


0

there is not a lot of information to go on but there can be a couple of reasons. the distro you are using doesn't have the mysql5.7 packages in its repository. You can check the /etc/yum.repos.d/ folder to verify what repositories are currently configured on your server. With yum info mysql you can ask some more information about the package available. If ...


7

The problem is that you've put the tilde inside double quotes. When you do this, it is not expanded to the path of your home directory. Consider: MacBook-Pro:~ error$ cat x.sh #!/bin/sh echo ~ echo "~" ls ~ ls "~" MacBook-Pro:~ error$ ./x.sh /Users/error ~ Calibre Library Downloads Music bin Desktop Library Pictures synergy Documents ...


2

You can compress the stream, but that may not help a whole lot. Why are you surprised that things take longer over the network? Speed up your network and/or speed up the device you're dumping to if you want to try and improve performance.


0

Your innodb buffer pool is too small. You need to increase it and like 4 other innodb parameters. Basically, you tell the innodb engine in the mysql config how much ram it gets. Presumably this is to keep horrible things from happening to your data if the machine runs out of ram. If you have a lot of innodb tables make the buffer pool really really large, ...


3

Network filesystems and databases can be a problematic combo, and at least InnoDB does not like NFS. From the MySQL documentation If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of ...


1

If you enable the slow query log in mysql, it will give you a starting point for any SQL statements that take a lot of resources. It won't tell you which PHP script was the culprit, but you will have the database, table and SQL, and the time of the problem; http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html The slow query log consists of SQL ...


0

If it is the case that mysql is the cause of the high load, then Mysql supports a slow_query_log argument, and once enabled it will log any particularly slow queries to a file; http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at ...


0

The procedure is different based on the MySQL server version. For version 5.0 and 5.1, run STOP SLAVE, CHANGE MASTER TO MASTER_HOST='' and then RESET SLAVE. In these versions the RESET SLAVE command will remove files master.info and relay-log.info and most of the settings, however some of the settings will just be reset to default values and this is ...


4

Posting this as an answer. It was a file permissions issue. U need to ensure that the mysql user has access to the mysql datadir and the directory it is in, all the way to the root level directory. If multiple users need access to these directories, then put them in a group and then give that group access to those directories.


4

Why don't you just put the ZFS dataset in the location MySQL expects? zfs set mountpoint=/var/lib/mysql tank/mysql Now you don't have to worry about strange permissions on /tank and everything looks normal to the rest of the system.


1

If you get an error from zcat, in which the error message contains the file name with an extra suffix .Z, then try using gzcat instead, as described at http://stackoverflow.com/questions/296717/zcat-wont-unzip-files-properly


1

Added innodb_force_recovery= 3 to my.cnf and it is now fixed. I incremented from 1 and it was fixed at 3.


0

What I did in the end was to send the time stamp from the server as char: SELECT 'server-time' AS category, CAST(TIME_FORMAT(CURTIME(), '%H.%i') AS CHAR) AS VALUE In grafana edited the metric to get only the max value: system.mysql_db.gauge-server_time-server-time:max Also in singlestat type in grafana under options tab: unit=short decimals=2 big value->...


0

You may try this if using MySQL 5.7 version mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip, @@hostname as hostname, @@port as port, DATABASE() as current_database; +-----------+-----------------+------+------------------+ | ip | hostname | port | current_database | +-----------+-----------------+------+------------------+ | ...


2

You can force MySQL to use no client certificate by setting an empty keystore in the connection URL. The problem I had above was a variant of https://bugs.mysql.com/bug.php?id=36948 -- if you set "clientCertificateKeyStoreUrl" in the connection URL but not also "trustCertificateKeyStoreUrl" (and the 4 other params below), then MySQL will crash with "...


1

After a lot of wrangling, the thing that finally fixed this for me was simply to create a missing /etc/mysql/conf.d/ directory (ie. sudo mkdir /etc/mysql/conf.d/). There was a /etc/mysql/mysql.conf.d/ directory, but MySQL seemed to be looking for a conf.d directory too, and its absence seemed to be preventing it starting. Once I'd created the directory sudo ...


0

Have a look at this site https://mathiasbynens.be/notes/mysql-utf8mb4 Although Jim's answer should also work.


0

I think your security group of ec2 instance must have outbound {All TCP } and select destination anywhere or to your ip


2

As mentioned in How can I see my IOPS burst credits? (source): Unfortunately there isn't a Cloudwatch Metric for the current IOPS Credit balance like there is with the CPU credits. This has still not been implemented as of July 2016. The AWS docs offer a pretty good introduction to IOPS burst: Each volume receives an initial I/O credit balance of ...


0

Why do think mysql is a bottleneck? Im pretty shure php is the issue here, since i manage lot of magento websites with more visits than 10k/day. Wp is 100%compatible with php7 so start from there. Do it in a development environment first then in live. Also a cacheing plugin is a good boost, i suggest redis cacheing simple super fast. Next step would be to ...


3

This should work: [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' May be you need to define these options as well: [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 Hope it helps. Cheers Update: Changed to utf8mb4


0

Here are the step by step instructions to reset the MySQL root password from their manual: Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is ...


0

For the sake of this answer, you want to replicate database mydb STEP 01 : Add Replication Filter to my.cnf on ServerD [mysqld] replication-do-db=mydb STEP 02 : Stop Replication on ServerD mysql> STOP SLAVE; mysql> FLUSH TABLES; mysql> FLUSH LOGS; mysql> SET GLOBAL innodb_fast_shutdown = 0; STEP 03 : Restart MySQL on ServerD service mysql ...


2

Typically if a MyISAM table has free space in the middle of the datafile after DELETE operations, it will insert the rows in that free space. However, due the way MyISAM implements concurrency, it depends on the workload what happens. From MySQL manual For a table with a hole, new rows are inserted at the end of the table if it is in use by another ...


1

If you have the FILE privilege you can, table by table, use SELECT ... INTO OUTFILE queries and download your data from those.


3

You can use the mysql_config_editor utility to store authentication credentials in an encrypted login path file named .mylogin.cnf. To create a new set of credentials run: mysql_config_editor set --host=db.host.org --user=dbuser --password and enter your password when prompted. This will store your authentication credentials in the default client login ...


0

Try these things: Try login by using vagrant as username instead of root, if that satisfies your problem. Other things you can try is: Changing the password for root. Check the users using: sudo mysql SELECT * FROM mysql.user Check if you have user named root then try: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; ...


0

In order to check your alias is working properly you can try to check your MySQL directly first. From your log I don't see any attempt delivering to your mailbox Select goto from alias For alias you should see something like this in your log 29 06:46:59 mail postfix/local[15339]: A8967BF907: to=<yourmailbox@domain.com>, orig_to=<youralias@...


2

Six years after this question was asked, I had to copy an 80 million row MYISAM table to a backup database (it was using 80% of the disk space of our whole database). I took the .MYI, .MYD, and .FRM files from the nightly backup, and copied them to the folder of a new running database. The information_schema was updated almost immediately, and all table ...



Top 50 recent answers are included