Tag Info

Hot answers tagged

8

The MySQL query cache does indeed cache the results of the query and as long as subsequent queries are exactly the same, the calculated result will be provided instead of actually running the query. MySQL will also skip calculating the execution plan. To use it, you can either turn it on in your server's my.cnf or use the SQL_CACHE hint in front of your ...


6

Yes, you are correct that if you don't specify --ssl-ca then the client does not check the server certificate at all. Since it works without that option the most likely reason for the failure is that the client doesn't trust the server certificate. If you are using self-signed client and server certificates then the ca.cert file should include both these ...


6

Here's how I fixed the problem: Step 1: By default, its impossible to find out the reason why the service is failing to start. So, tell it to start in non-service mode, and pipe errors to the console: mysqld --console Step 2: In my particular case, I had to delete the contents of: C:\ProgramData\MySQL ... then reinstall. The MySQL v5.5 installer has a ...


6

Unless you take the appropriate precations this is a very real problem. Briefly, your configuration on each server needs two values set. auto_increment_increment auto_increment_offset auto_increment_offset should be set such that each master has a unique value, normally being 1 for the first, 2 for the second, etc. auto_increment_increment is the step ...


6

That's actually remarkably good - the higher the better. The MySQL performance is good if the value of Key Read Efficiency is 90 percent and above http://www.webnms.com/mysql_agent/help/mysql_agent/performance.html


4

Well, the answer is right in that table - you need to remove the "root" lines that have no password listed. Also, looks like you allow connections from anywhere (?!?!) when using no user or pass. Probably a bad idea. I'd do something like: mysql> use mysql; mysql> delete from user where password = "";


4

MySQL automatically attempts to do a reverse DNS lookup of any IP's that try to connect to it, and the DNS name is what gets checked against your grants. If you start up mysqld with the --skip-name-resolve option, it will skip DNS lookups and match against IP addresses instead. Reference: http://dev.mysql.com/doc/refman/5.0/en/host-cache.html


4

The straight-forward course of action is to wipe out the data on the slave and start fresh with a new copy of the master. The master can be copied by using mysqldump or through something like rsync. Depending on how large your database is, you may experience downtime. This will always work. There's a chance that if you clear space off the slave drive you ...


4

In MySQL command line, give command SHOW SLAVE STATUS\G and see the Seconds_behind_ master value. Or if you want to script that somehow, the one-liner below returns how many seconds behind master the slave currently is. mysql -u your_user -e 'SHOW SLAVE STATUS\G' | grep "Seconds_Behi" | awk '{ print $2; }'


3

The standard way to do this is to assign an autoincrement columnn to each of these tables, and increment them by an interval - 10, in your case. Each server will use a unique offset into the interval, so server 1 will use IDs 1, 11, 21 etc, server 2 will use IDs 2, 12, 22, etc. This ensure they will never overlap. Then you only need to set up replication ...


3

If you are using Fedora you might want to use chkconfig. To see what run levels a particular service is enabled for, you can do something like this chkconfig --list <servicename> Ex: chkconfig --list mysqld To change the behavior for certain run levels for a service you can do something like this: chkconfig --levels 345 mysqld on or since ...


3

just stop slave, and skip the sql statement that makes duplicate entry using (http://dev.mysql.com/doc/refman/5.0/en/set-global-sql-slave-skip-counter.html) mysql> stop slave; mysql> SET GLOBAL sql_slave_skip_counter = 1; mysql> start slave; if the error is different after you are running the above command, just repeat it again until the slave ...


3

I'm part of the Cluster team in MySQL, and have some resources to share with you in determining whether MySQL Cluster is a good fit To learn more about cluster in web apps, there is this guide which covers the architecture of Cluster and common use cases in the web (note, registration required): ...


3

You could go about this several ways. There are likely other ways to go about this, but these 2 spring to mind instantly. You could create another table to hold your cached results and on a schedule run the expensive queries which might join multiple tables with lots of records in them and dump the records into this table. Doing a SELECT * from a single ...


3

if you use innodb engine on the master - just use mysqldump with --skip-lock-tables, --single-transaction and --master-data=2 switches. if your slave is only logically corrupted - try pt-table-sync with lock=0 option


3

The -A (or equivalently --no-auto-rehash) is to be added as an option to mysql when starting it from a command line, e.g.: mysql -p -u myusername -A Are you running mysql from a shell prompt, or through some web admin interface?


3

Experimenting was the order of the evening... mysql> set @@global.expire_logs_days=0.75; ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days' mysql> set @@global.expire_logs_days=.75; ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days' mysql> set @@global.expire_logs_days=3.4; ERROR 1232 (42000): Incorrect ...


3

what is a mysql 'packet', anyway That page you linked to describes exactly what a packet is. It is: A communication packet is a single SQL statement sent to the MySQL server A single row that is sent to the client A binary log event sent from a master replication server to a slave. So, if your packet size is set to 16Mb, then you won't be able to ...


3

Using MySQL 5.5.9, 5.5.11 in production Linux environments, here are my observations for using the InnoDB Buffer Pool, handling BLOBs, Setting Caches, Diskspace Usage and other assorted rantings . It applies just as much in Windows.


3

The answer with MySQL is always, "it depends". That being said where 5.5 is going to give you a huge improvement on boxes with 16 cores and higher. If you use the InnoDB storage engine, then there is a good chance that you will get better performance too. The best way to find out is to get a test box and run it through a performance test of your system. As ...


3

No hints? [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 bind-address = localhost skip-networking enable-named-pipe <------------------- [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid According the ...


2

I agree with @faker's answer's and comments (+1 for his answer), but there is still one major thing to do: Before you shutdown mysql, you need to run SET GLOBAL innodb_fast_shutdown = 0;. Why? This will completely purge all transactional information left over in ib_logfile0 and ib_logfile1. The shutdown time is longer, but nothing in terms of transactional ...


2

I would recommend a mysql 5.1 binary from: http://dev.mysql.com/downloads/mysql/5.1.html You can select freebsd from the dropdown. Then follow the directions at: http://dev.mysql.com/doc/refman/5.1/en/binary-installation.html These instructions are not bulletproof, so you may need to read a bit of documentation. Post questions if you have them.


2

You can still do a lot in MySQL alone to gain performance on this setup, I'm using MySQL primer on my servers to fine-tune all memory related parameters (you can get it from here). Also the filesystem is important but not critical for MySQL, I've been using it both in reiserfs and ext3 (try to add noatime,nodiratime in /etc/fstab if using ext3) with almost ...


2

This question is very confused. Title suggests problem connecting to port 3306 (which is where mysql usually hangs out). There is no list of active services. What does a service at port 80 have to do with anything? I have to use http://localhost:81 for my root folder. Why do you have to use port 81? What do you mean by your "root folder". I suspect ...


2

I do not see why "this sounds too good to be true". Considerinh PHP 5.2 was released quite a few years ago, the problem is that Redhat/CentOS are really late about that (yeah, I know, "stable" and all that, which means not updating anything except for security reasons) Happily, some people stepped up, and did the job that had to be done : create the RPM ...


2

The FAQ explains: Start the server with the --replicate-wild-ignore-table=mysql.% option. You can also use this as a my.cnf option. Typically we do replicate GRANTs. because we often want slaves to behave as identical read-only copies of the master. This way our applications can switch seamlessly between DB servers for RO operations. You just have ...


2

Don't use replicate-do-db and replicate-ignore-db, because those options are dangerous. look at this article. Just use --replicate-wild-ignore-table=mysql.% option. I would reccomend you to set --replicate-wild-ignore-table=mysql.user - this skip only password and global grants replication, while table and column level grants are still replicated. You can ...


2

As a MySQL DBA, I have never trusted mysql_upgrade. The Achilles' heel of moving data from one release of MySQL to a higher release of MySQL is the risk of messing up the tables in the mysql schema. The best thing you can do is dump all databases except information_schema database (mysqldump ignores that by default) and the mysql database. How can one ...


2

To use one way ssl, you should try with: mysql -u <user> -p --ssl=1 --ssl-ca=ca.cert --ssl-verify-server-cert The --ssl-cert and --ssl-key on the mysql client are used for 2 way SSL. This means certificate based authentication. The subject of the client certificate should be the username.



Only top voted, non community-wiki answers of a minimum length are eligible