Re: Replication config
replying only to the list... On Wed, May 13, 2009 at 10:19:21AM -0700, Scott Haneda wrote: > >3. Configure on the slave the replication (which databases need to > >be replicated) > > This is where I need a little clarification, is the only thing I need > to do is adjust my.cnf to have in the [mysqld] section > server-id = 2 That's the minimal configuration. You may need to specify which databases need to be replicated or which tables. By default everything is replicated which is probably fine. > >4. Get the master and slave in sync (via rsync, load/dump or whatever) > > Is this mandatory? There is not a lot of data, hundred rows or so, > can I use LOAD DATA FROM MASTER; ? I think that only works in MySQL 4, and have never used it on our production servers (5.0). Yes, checking the MySQL 5. documentation it says: http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html --quote-- 12.6.2.2. LOAD DATA FROM MASTER Syntax LOAD DATA FROM MASTER This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL. --quote-- If you've only got hundreds of rows then just do a normal mysqldump. The problem is that if you have a database with GB or hundreds of GB of data then this process is really slow, and if at the same time you can't afford to stop your master then that makes life harder. > Seems most instructions say to use a dump. This does not make a lot > of sense to me, I am setting up replication, is it not the point to be > able to pull the data down? Why does it need "priming" like this? For 5.0 and above because the you can't load DATA from master, so just use the dump, and don't change the master while you are doing this. > >5. Run show master status on the master (assuming binlogging is > >enabled) > > to get the current position on the master > > I can do this now, gives back a position. It seems to change over > time. Since it is a moving target, if I am using LOAD DATA FROM > MASTER; I take it I need to lock the tables while the first load is > happening? If you're using 5.0 you shouldn't be using LOAD DATA FROM MASTER. Do a mysqldump and load from that, or if you use some sort of unix with snapshotting possibilities then make a (lvm) snapshot of the filesystem and copy that. That's what we typically do at work and it leaves the master down for just a second or so. The later copy can take place while the master is running. > >6. use CHANGE MASTER TO on the slave providing the appropriate > >permissions. This just tells the slave where to start replicating from. That is which statements or rows in the binlog to download from the master and apply on the slave. > >7. Run: START SLAVE This starts the replication process. > While not part of my plan, if the master goes down and I want to start > using the slave as the master while I am fixing the master server > > What is the best way to do this? Can the slave be treated like a > master by just pointing any client to the slave assuming I set a user > to allow it? You can do this from the point of view of the database users but then the slave will be more up to date than the master and if you've not configured things properly and don't have the right information you won't be able to get the master back in sync. So you can't just switch between boxes without taking special care. > With the slave temporarily becoming the master, the data will of > course change. When I bring the master back online, what is the best > way to reverse sync and get back to where I was? Probably take the > entire thing thing down, copy the database from the current temp live > slave that has been used as a master, and go from there? If the end that may be necessary. You can configure master / master replication but as I said you have to be careful with this as it can be quite critical how you actually setup your tables. If you don't do things correctly it won't work. I think it is documented however in the MySQL manual so I'd suggest you read that. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
using mysqld_multi - reboot only session 1 starts.
We are using mysqld_multi to start three instances, the command /usr/ bin/mysqld_multi --config-file=/etc/my.cnf start 1-3 works just fine. But when I tried to put that in rc.local only 3306 would start. Ok fine. So I created 3 scripts that I put in /etc/init.d called mysqld1, 2, and 3. So now /sbin/service mysqld start | stop | status all work just fine. I then added them to run level 3. I can stop, start, and status any or all but when I reboot I have a problem. $ sudo /sbin/service mysqld status Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is not running MySQL server from group: mysqld3 is not running $ sudo /sbin/service mysqld2 start $ sudo /sbin/service mysqld status Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running MySQL server from group: mysqld3 is not running $ sudo /sbin/chkconfig --list | grep mysql mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off mysqld1 0:off 1:off 2:off 3:on4:off 5:off 6:off mysqld2 0:off 1:off 2:off 3:on4:off 5:off 6:off mysqld3 0:off 1:off 2:off 3:on4:off 5:off 6:off $ sudo tail /var/log/mysql/mysqld3.log 090513 16:06:50 [ERROR] Can't start server: Bind on TCP/IP port: Permission denied 090513 16:06:50 [ERROR] Do you already have another mysqld server running on port: 3308 ? 090513 16:06:50 [ERROR] Aborting 090513 16:06:50 InnoDB: Starting shutdown... 090513 16:06:52 InnoDB: Shutdown completed; log sequence number 0 195828884 090513 16:06:52 [Note] /usr/libexec/mysqld: Shutdown complete 090513 16:06:52 mysqld ended I have this same error but for port 3307 for mysqld2 but as you can see it starts just fine when I run it after the server is up, why? Any help greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Creating a New DB / User
You would be fine. Grant all does not include with grant option. Ability to grant has to be given explicitly. Kyong At 08:57 AM 5/13/2009, Carlos Williams wrote: On Wed, May 13, 2009 at 11:15 AM, Michael Dykman wrote: > One thing though: your user does not need the 'WITH GRANT OPTION' bit > as that gives them permission to gratn permissions to other users, > which I don't think is what you want. How do I change the bit for 'grant all' via the CLI? What command would I run to keep the user as having full rights to the 'dublin' database but not being able to grant other users permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kimky...@fhda.edu Kyong Kim Instructional Multimedia/Web Programmer Foothill College 12345 El Monte Rd 3601 Los Altos Hills, CA 94022 650-949-7091 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
Thanks, a few comments inline below... On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, #MASTER_USER=, MASTER_PASSWORD= ; # #where you replace , , by quoted strings and # by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. Done, I can see the binlogs as well. 2. Ensure you setup grant permissions so the slave can connect to the master. From the remote MySql I have mysql -u user -h example.com -p It allows me in so I think I am good there. 3. Configure on the slave the replication (which databases need to be replicated) This is where I need a little clarification, is the only thing I need to do is adjust my.cnf to have in the [mysqld] section server-id = 2 4. Get the master and slave in sync (via rsync, load/dump or whatever) Is this mandatory? There is not a lot of data, hundred rows or so, can I use LOAD DATA FROM MASTER; ? Seems most instructions say to use a dump. This does not make a lot of sense to me, I am setting up replication, is it not the point to be able to pull the data down? Why does it need "priming" like this? 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master I can do this now, gives back a position. It seems to change over time. Since it is a moving target, if I am using LOAD DATA FROM MASTER; I take it I need to lock the tables while the first load is happening? 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE Will do, thanks. 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Thanks. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. While not part of my plan, if the master goes down and I want to start using the slave as the master while I am fixing the master server What is the best way to do this? Can the slave be treated like a master by just pointing any client to the slave assuming I set a user to allow it? With the slave temporarily becoming the master, the data will of course change. When I bring the master back online, what is the best way to reverse sync and get back to where I was? Probably take the entire thing thing down, copy the database from the current temp live slave that has been used as a master, and go from there? Thank you. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trigger working with server 5.0.51 but not 5.0.22
The DROP TRIGGER command is in the wrong place. You changed the delimiter to $$ but still tried to use the semicolon(;) with DROP TRIGGER. Your code should read like this: DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; DELIMITER $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Or change the ; to $$ on the DROP TRIGGER command like this: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Give it a try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Mauricio Tellez [mailto:mauricio.tel...@gmail.com] Sent: Wednesday, May 13, 2009 4:35 AM To: mysql@lists.mysql.com Subject: Trigger working with server 5.0.51 but not 5.0.22 Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got *"**SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist" Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Creating a New DB / User
2009/5/13 Pintér Tibor : > how about reading the fine manual of the GRANT command at mysql.com? I did to avoid being lazy...go figure. I then decided to ask here before I make a mistake & I felt the fine manual was not clear enough for me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Creating a New DB / User
Carlos Williams írta: On Wed, May 13, 2009 at 11:15 AM, Michael Dykman wrote: One thing though: your user does not need the 'WITH GRANT OPTION' bit as that gives them permission to gratn permissions to other users, which I don't think is what you want. How do I change the bit for 'grant all' via the CLI? What command would I run to keep the user as having full rights to the 'dublin' database but not being able to grant other users permissions? how about reading the fine manual of the GRANT command at mysql.com? t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Creating a New DB / User
On Wed, May 13, 2009 at 11:15 AM, Michael Dykman wrote: > One thing though: your user does not need the 'WITH GRANT OPTION' bit > as that gives them permission to gratn permissions to other users, > which I don't think is what you want. How do I change the bit for 'grant all' via the CLI? What command would I run to keep the user as having full rights to the 'dublin' database but not being able to grant other users permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select Into OUTFILE problem
I have a bit of perl code that ends with an error: $sql="SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC"; $sth = $dbh->prepare($sql); $rv = $sth->execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Creating a New DB / User
Your directive looks fine assuming dublin expects to always connect from localhost. One thing though: your user does not need the 'WITH GRANT OPTION' bit as that gives them permission to gratn permissions to other users, which I don't think is what you want. - michael dykman On Wed, May 13, 2009 at 11:05 AM, Carlos Williams wrote: > I have used MySQL twice in my career so I did read the MySQL How-to > page and just wanted to ask here if what I did as correct based on > what I wanted to do. I just wanted to create a new database in MySQL > called "webmail" and then also create a new user who limited to only > accessing the 'webmail' database and nothing else. Can someone please > tell me if I did this correctly or if I messed up and mis-understood > the instructions. I am sure there are easier ways to do this via > mysql-admin tool but I prefer to just try the way below unless its > dead wrong... > > === > > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 437 > Server version: 5.0.45 Source distribution > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> show databases; > ++ > | Database | > ++ > | information_schema | > | cal | > | forums | > | it | > | mysql | > ++ > 5 rows in set (0.00 sec) > > mysql> create database webmail; > Query OK, 1 row affected (0.00 sec) > > mysql> grant all privileges on webmail.* to dub...@localhost > identified by 'n...@ke$' with grant option; > Query OK, 0 rows affected (0.00 sec) > > === > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Creating a New DB / User
I have used MySQL twice in my career so I did read the MySQL How-to page and just wanted to ask here if what I did as correct based on what I wanted to do. I just wanted to create a new database in MySQL called "webmail" and then also create a new user who limited to only accessing the 'webmail' database and nothing else. Can someone please tell me if I did this correctly or if I messed up and mis-understood the instructions. I am sure there are easier ways to do this via mysql-admin tool but I prefer to just try the way below unless its dead wrong... === Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 437 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; ++ | Database | ++ | information_schema | | cal| | forums | | it | | mysql | ++ 5 rows in set (0.00 sec) mysql> create database webmail; Query OK, 1 row affected (0.00 sec) mysql> grant all privileges on webmail.* to dub...@localhost identified by 'n...@ke$' with grant option; Query OK, 0 rows affected (0.00 sec) === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger working with server 5.0.51 but not 5.0.22
2009/5/13 Mauricio Tellez : > Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I > tried to move this to a production server (version 5.0.22 fedora) I ran into [...] > but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html "The IF EXISTS clause was added in MySQL 5.0.32." Greetings, mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trigger working with server 5.0.51 but not 5.0.22
Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got *"**SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist" Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez
