Tag Info

Hot answers tagged

115

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function: mygrants() { mysql -B -N $@ -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR ...


13

There are two methods for extracting SQL Grants from a MySQL Instance METHOD #1 You can use pt-show-grants from Percona Toolkit MYSQL_CONN="-uroot -ppassword" pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql METHOD #2 You can emulate pt-show-grants with the following MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A ...


11

As far as your specific question, it's a combination of two things: By default, if a server receives a statement via replication, it will not send that same statement out to its slaves, preventing any kind of loop. This setting however, can be changed (by adding 'log-slave-updates' to the my.cnf), leading to: In replication, it sends the server-id of the ...


11

It is entirely possible to give yourself mysql permissions without knowing SQL GRANT commands. Example : Here is to create your own user with full privileges using SQL GRANT from anywhere called superdba with a password of ClarkKent: GRANT ALL PRIVILEGES ON *.* TO superdba@'%' IDENTIFIED BY 'ClarkKent' WITH GRANT OPTION; Here is how you can do this ...


10

This is how i did it for both masters log-bin = mysqld-bin binlog-ignore-db=test binlog-ignore-db=information_schema binlog-ignore-db=mysql log-slave-updates replicate-ignore-db=test replicate-ignore-db=information_schema replicate-ignore-db=mysql relay-log=mysqld-relay-bin


9

There is a specific reason why what you proposed is impossible to achieve with MyISAM and InnoDB. A star topology warrants a Master being the center of the universe, not the slave. MySQL Replication was not designed to have a slave read from multiple masters simultaneously. It can only read from one master at a time. The CHANGE MASTER TO command connects a ...


9

i assume you use innodb as a storage engine. if so - you need to turn on bin-logging; if it's not on now - you need to restart mysql after changing my.cnf. it is the only downtime, after it you can take dump of the database with binlog position without blocking the database: mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob ...


9

use mysqldump --opt <database> <tablename> to create a dump of your table and feed it to your new server. As you apparently have access to the remote database via TCP/IP, you simply could use mysqldump --opt --user=<youruser> --password=<yourpassword> -host <yourhost> \ <yourDB> <yourtable> | mysql -u ...


8

There is no one-size-fits-all answer to scaling MySQL. A few general tips: Scale "diagonally" as long as you can, ie. keep things on a single MySQL server as long as you're still able to run on commodity hardware. That probably means 2 x quad-core CPUs, 64+ GB RAM, 8 disk RAID 10 -- or higher. The upper end of what is "commodity hardware" is getting faster ...


8

1 you can use maatkit's mk-heartbeat 2 you can look at result of show slave status; run on sql slave but Seconds_Behind_Master is disturbingly inaccurate at times. 3 you can hack your own solution, similar to mine - i use it both for nagios monitoring and for feeding munin charts showing 'seconds behind master'. on master server i have simple cron ...


7

Richard Bronosky's answer was extremely useful for me. Many thanks!!! Here is a small variation which was useful for me. It is helpful for transfering users e.g. between two Ubuntu installations running phpmyadmin. Just dump privileges for all users apart from root, phpmyadmin and debian-sys-maint. The code is then mygrants() { mysql -B -N $@ -e "SELECT ...


7

The binlog on DB2 wasn't updating the slave updates. To daisy chain the replication, one must set log-slave-updates in my.conf. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates


6

We use replication across datacenters in several European countries (so they aren't across the world from each other, but they are certainly not local) and it works without any problem. Replication will automatically restart if possible. If there is a problem with a query (e.g. a database is present on the master and not the slave, and a query uses it), ...


6

Easiest thing to do would be to do a mysqldump on your linux box, and then import said dump into your local windows database. First, the mysqldump (docs: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) mysqldump -u [username] -p [any other options desired] [database name] > dump.sql The above creates a file called "dump.sql" in the directory ...


6

Welcome to the wonderful world of MySQL replication. I haven't hit your particular issue myself, but I've hit a lot of other weird problems and the proximate solution is to just resync from the master as though it's a brand new slave and be done with it.


6

MySQL 5.5 has semi-synchronous replication. Basically it guarantees that at least one slave has received the transaction before it is commited. http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html


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

What to try to fix your problem: You should remove master.info on slave first and restart mysql issue CHANGE MASTER TO MASTER_HOST='XX.XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='slavepass'; do mysqldump with '--flush-logs' option on master 'mysql -u user -p < dump.sql' on slave 'show slave status\G' on slave to ensure that it is properly ...


5

If you look at the MySQL documentation, you will notice the following line: Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option. Further details clarify the above statement: The syntax for ...


5

The topology you're describing is called "circular replication". You're basically setting up a ring in which each node acts as a master to the following one. The authors of High Performance MySQL advise against this topology, with the following reasons: it depends on each node being available, therefore increasing your failure probability. if you remove a ...


5

In MySQL replication, a slave "syncs" its data from the master's transaction log, not directly from the database. The slave stores the last position in that transaction log, so as long as you have not touched that position number on the slave, then as soon as it becomes active again, it will resume executing transactions from that position it left off at. ...


5

You can mysqldump the 'mysql' database and import to the new one; a flush_privileges or restart will be required and you'll definitely want to back up the existing mysq db first. To avoid removing your existing privileges, make sure to append rather than replace rows in the privilege tables (db, columns_priv, host, func, etc.).


5

Or, utilize percona-toolkit (former maatkit) and use pt-show-grants (or mk-show-grants) for that purpose. No need for cumbersome scripts and/or stored procedures.


5

Yes, it is possible :) This is called "Master with Relay Slave" replication and there a lot of documents about it on the net. I'd recommend you to take a look at the official documentation here. Btw, also take a look at these slides. They've some hints about replication topologies. Hope this helps.


5

Due to a reasonably long delay with no answers, I have found out by trial and error. The answer is : Yes, I can run mysqld on top of glusterfs. I set this up on Ubuntu and briefly here are the steps: Install Ubuntu Install and configure glusterfs Install mysql-server Configure /etc/mysql/my.cnf to set datadir /mnt/glusterfs instead of /var/lib/mysql Set ...


5

I received a hint in a comment but the user has since deleted it. The trick is to not use replicate-do-db and only use replicate-rewrite-db: #replicate_do_db='DB1' replicate-rewrite-db=DB1->DB2 Commenting out the replicate_do_db line in my.cnf fixed it for me. Thanks to the mystery user who pointed me in the right direction, whoever you are.


4

According to your slave status, replication is up and running correctly. What is likely to be broken are your expectations. MySQL replication is a one-way replication from master to slave without the verification of consistency on the slave. Rows changed on the slave by a third party will not get replicated back to the master but will obviously affect ...


4

My question is what are the best practices to monitor and / or test that the slave databases are up-to-date, For a simple testing, insert/update data on the master and make sure that it is replicated to the slaves. But for the consistency check, pt-table-checksum is what you're looking for. For e.g: pt-table-checksum localhost ...


4

Inspect the relevant traffic with tcpdump/wireshark.


4

You are looking at your slave's own binary logs. Your slave has clearly the log_bin variable set, and it's filling its disks with it's own binary logs (not the master's logs, their names probably contain a relay word, since you are using defaults in binary and relay log names). You can view them with show master status. To solve this problem (if you don't ...



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