Tag Info

Hot answers tagged

8

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 ...


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 ...


8

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

RDS instances can change their IPs unexpectedly, so they should not be used nor are they provided in the console or API (although you can technically dig for them). The DNS endpoint provided in the AWS console will resolve to the internal IPs from within Amazon's network.


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


5

Run the following command mysql_install_db


4

Test with mysqlslap.


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; }'


4

You need to simply remove the anonymous user. In the future, running /usr/bin/mysql_secure_installation (Location in RPM Installation) will prompt you to remove these anonymous users. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended ...


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

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 ...


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

I can't explain why the service disappeared, but I did figure out what happened to the data. Somehow, my my.ini file got completely wiped out. Once I got a copy of that file, I saw that the datadir value was set to C:/ProgramData/MySQL/MySQL Server 5.6/data\ This is not the default location of C:\Program Files\MySQL\MySQL Server 5.6\data. It is, however, ...


4

You can use the same command, but after adding the DB name like: $ mysql -u root -p db1 -e "your-insert-here"


3

You can just add a use db1 command to the string you pass to mysql. mysql -u root -pz -e "USE db1; INSERT INDO role_permissions values (1,'create about_us content','node');" Also, did you create your role_permissions table e.g. mysql -u root -pz -e "USE db1; CREATE TABLE role_permissions (id INT, data VARCHAR(200), type VARCHAR (10);"


3

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.


3

mysql> select * from information_schema.tables where table_name='foo'\G


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

It was definitely a memory issue. This became evident once I looked in /var/log/messages. I was trying to run recent versions of Nginx, PHP-FPM, and MySQL, and the default configurations for all 3 of these together were too much for my little droplet that had only 512 MB of memory and no swap space. I tweaked my /etc/my.cnf and my PHP-FPM config, and ...


3

No. Variable could be enabled in option file my.cnf, otherwise after restart it will be reset to default value 1


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 ...


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

Looks like the password is set on the 'root'@'localhost' user entry, but not on the 'root'@'%' entry; password-free authentication would be allowed based on that. For security purposes, reconsider allowing root access from anywhere. If you do need it, then just get rid of the localhost specifications: drop user 'root'@'localhost'; drop user ...


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

make install clean -C /usr/ports/database/mysql51-server http://www.freebsd.org/doc/en/books/handbook/ports.html


3

Use binary package(it's easier than to compile from ports): pkg_add -r mysql51-server


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 ...



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